Postgresql – Changing numeric type slow down query big time for postgres

join;performancepostgresqlpostgresql-9.6query-performance

I have 3 tables, leads, lead_addresses and addresses. Scheme looks like this:

recently we decided that foreign keys would be beneficial for us so we changed data types for lead_id and address_id from numeric to integer in lead_addresses table (in scheme named LeadAddresses). Reason for data type change is because foreign keys cannot be created between id which is integer and lead_id for numeric. Same for address attributes.

All tables mentioned have 10k+ records.

Problem:

We have query which works with numeric types well, but with integer types takes 20s+. What it does, its selects all leads and join addresses (only one address per lead, the last address created).

EXPLAIN ANALYZE 
SELECT 
  lead.id,
  lead.updated_at,
  lead.state,
  CONCAT(address.street, ', ', address.city, ', ', address.state) as address 
FROM leads AS lead 
LEFT JOIN lead_addresses lead_address ON (
  lead_address.lead_id = lead.id AND
  lead_address.id = (
     SELECT id FROM lead_addresses WHERE lead_id = lead.id ORDER BY id desc LIMIT 1
  )
)
LEFT JOIN addresses address ON (address.id = lead_address.address_id)
WHERE (lead.state = 1)
ORDER BY "lead"."updated_at"DESC
LIMIT 15000

We have original database which is in production and execution is fast. Here is result of explain: https://pastebin.com/raw/F6XaKNzp

We made exact clone of same database got rid of indexes on LeadAddresses
and changed types from numeric to integer, build indexes again.

Here is result of explain again: https://pastebin.com/raw/LhAyJ4ZQ
this time it takes too long.

I noticed the subplan being executed for each row, on the other hand in old production database its fast since subplan is executed once.

Can anybody give me advice on how to make planner execute subplan only once? how to change datatype of tables correctly?

Here are indexes:

Order of operations to create test db (the one where query is slow):

1) we made clone of database from old production

2) we executed query. Query was fast same as in production database

3) we dropped indexes on join table (LeadAddresses)

4) we changed numeric types to integer

5) we build same indexes again

6) query is still slow :'(

I'll be glad for any advice. Thanks.

Best Answer

I would like to point out that the join condition in the EXPLAIN was still comparing ::numeric with ::integer as can be seen in the EXPLAIN PLAN you provided:

...
Hash Cond: (((lead.id)::numeric = lead_address.lead_id)
...
 Hash Cond: ((address.id)::numeric = lead_address.address_id)
...

So even though you seemed to have dropped, converted and re-created the relevant information, your query was still creating an execution plan based on old information.

Cleaning up the database with VACUUM ANALYZE ... rectified your issues.

1. Table partitioning

Because of the [AND traffic.time >= 1343772000 AND traffic.time < 1346450399] clause, I imagine that you never delete data from this table, or that the table is currently storing data for multiple months. The values in the column [time] seem to be unix timestamps (1346450399 = Fri, 31 Aug 2012 21:59:59 GMT) Partition the table based on time column. That will speed up the data retrieval, as the DB will scan the coresponding partition (much faster then to scan whole table).

A great partitioning tutorial can be found here: the http://www.arachna.com/roller/spidaman/entry/scaling_rails_with_mysql_table
You will need to calculate the timestamp ranges for that, but it should not be hard to do.
Ex: (1346450399 - 1343772000) / 60 / 60 / 24 =~ 31 days. So the max value for the partition holding the data for september (has also 31 days) will be: 1346450399 + ( 31 * 24 * 60 * 60)
An unix to date calculator can be found here: http://www.onlineconversion.com/unix_time.htm

2. Rewrite the query

Because the "OR" in your WHERE block, the optimizer will choose not to use the index defined. Try to split the query in 2 selects, and make an union.

SELECT 
    traffic.time, 
    SUM(traffic.bytesin), 
    SUM(traffic.bytesout) 
FROM 
    traffic 
WHERE traffic.type LIKE 'v4_assignment' 
    AND type_id IN (1,2,3,4)
    AND traffic.time >= 1343772000 AND traffic.time <= 1346450399 
GROUP BY 
    traffic.time
UNION
SELECT 
    traffic.time, 
    SUM(traffic.bytesin), 
    SUM(traffic.bytesout) 
FROM 
    traffic 
WHERE traffic.type LIKE 'v4_host' 
    AND type_id IN (5,6,7,8)
    AND traffic.time >= 1343772000 AND traffic.time <= 1346450399 
GROUP BY 
    traffic.time
ORDER BY 
    traffic.time

3. New index based on data cardinality

Based on your explain output, I don't see the index beeing used. Maybe because the optimizer decides that it will be easier (cheaper) to make a full table scan then to follow the index. Also, in your current index, the first column has a lower cardinality then the next 2 ones. The first column in any index should be the column with the best (max) cardinality.

Create a new index as:

MYSQL> CREATE INDEX MTIhai_traffic_idx1 ON traffic(time, type, type_id)

Postgresql – Slow PostgreSQL query

Looks like (I'm by no means a Postgres expert) you have about two million rows in LEAD_DETAIL table that satisfy the state condition... those two million rows are retrieved, hash joined to leads, the result sorted to return the top 5000 rows. Could you move the state to leads table, so that it becomes a column there? Create and index on (upper(state), created_date_time) and try again (of course, rewriting your query to use the new state column in leads table).

And by the way, most of your indexes on leads table are useless. Why have lead_created_date_time_idx and lead_created_date_time_idx1 - they are the same, an ascending index can be used also for DESC sorts. Furthermore, why include lead_id in indexes (except the one supporting the primary key constraint)?

Best Answer

Related Solutions

Mysql – How to improve the speed of a query on a 20 million+ row table

1. Table partitioning

2. Rewrite the query

3. New index based on data cardinality

Postgresql – Slow PostgreSQL query

Related Question