Hail there,
Short question:
Why would pg optimizer choose a worst (slower) query plan for a
query with 'LIMIT 1' instead of, say, 'LIMIT 3'?
Complete scenario:
Query: 'SELECT * FROM a WHERE a.b_id = 42 ORDER BY created LIMIT 1'
- b_id is a FK to b;
- created is a datetime with the time of the creation of the row;
- both 'b' and 'created' are indexed separately
This query, with the LIMIT 1, uses the index on created, which is much
slower (10x) than if it used the index on b_id
If I change the LIMIT from 1 to 3 pg starts using the index on b_id.
Already tried running REINDEX and VACUUM ANALYZE on both A and B.
Nothing changed.
Why does this happen?
Is there any way I can hint/force the optimizer to use b_id index?
Thanks