Changing numeric type slow down query big time for postgres 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.
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.