Re: much slower query in production

By the way, I expect the time is cut in half while heap fetches stays
similar because the index is now in OS cache on the second run and didn't
need to be fetched from disk. Definitely need to check on vacuuming as
Justin says. If you have a fairly active system, you would need to run this
query many times in order to push other stuff out of shared_buffers and get
this query to perform more like it does on dev.

Do you have the option to re-write the query or is this generated by an
ORM? You are forcing the looping as I read this query. If you aggregate
before you join, then the system should be able to do a single scan of the
index, aggregate, then join those relatively few rows to the multicards
table records.

SELECT transaction_uid, COALESCE( sub.count, 0 ) AS count FROM multicards
LEFT JOIN (SELECT multicard_uid, COUNT(*) AS count FROM tickets GROUP BY
multicard_uid ) AS sub ON sub.multicard_uid = multicards.uid;