I have 3 tables,
addresses. Scheme looks like this:
recently we decided that foreign keys would be beneficial for us so we changed data types for
lead_addresses table (in scheme named
LeadAddresses). Reason for data type change is because foreign keys cannot be created between
id which is
numeric. Same for address attributes.
All tables mentioned have 10k+ records.
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
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?
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 (
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.