0

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

scheme

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: 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.

  • I have a couple of ideas how to optimize storage and performance beyond what you have so far. If you provide CREATE TABLE and CREATE INDEX statements to makes this easier (maybe in a fiddle with a couple of sample rows) ... (And remove the image showing indexes: this kind of information should always be text.) – Erwin Brandstetter May 29 '18 at 22:08
1

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.

0

So apparently VACUUM ANALYZE leads, VACUUM ANALYZE addresses and VACUUM ANALYZE lead_addresses helps.

Never mind then :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.