I had an interesting problem occur to the pg stats. We were migrating and had a version column, I.e key, val, version.
We were migrating from version 1 to version 2, double writing into the same table. An index on (key, val, version) was being hit by our reader process using a where clause like key=k and version=1.
When we flipped the reader to read version 2, the latency jumped from 30ms to 11s. Explain showed a sequential scan even though the index could serve the query. I was able to use MATERIALIZED and reorder CTEs to get the planner to do the right thing, but it caused an outage.
We were autovacuuming as well. I ended up deleting the old version and rebuilding the index.
My theory is that because the read load was on 50% of the data, the stats were super skewed.
We were migrating from version 1 to version 2, double writing into the same table. An index on (key, val, version) was being hit by our reader process using a where clause like key=k and version=1.
When we flipped the reader to read version 2, the latency jumped from 30ms to 11s. Explain showed a sequential scan even though the index could serve the query. I was able to use MATERIALIZED and reorder CTEs to get the planner to do the right thing, but it caused an outage.
We were autovacuuming as well. I ended up deleting the old version and rebuilding the index.
My theory is that because the read load was on 50% of the data, the stats were super skewed.