[lebski88]

Yes this is completely my experience too. I spent most of yesterday battling with the query planner. Sometimes you can't express with table statistics something that you know to be true for the exact query that you're making.

There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performance. What worries me is that the table stats cross a threshold at 3am and suddenly the query planner chooses something crazy.

I've also wondered if an approach of trying a bunch of query plans could be fun. Get it to pick the top 10 possibilities and just run them all and record stats on which was fastest. Doubly so if it ever decides to run a seq scan where there is an index. Please. Just try the index! That said yesterday I was definitely at the point where I just wanted to express the exact query plan myself.

[jauntywundrkind]

Very memorable post about this happening at Bluesky, about being utterly unable to trust Postgres to keep doing what it's doing.

> Bluesky scaling lesson: Postgres is not a great choice if your data can be irregular.

> The query planner will switch over to a new plan that consumes 100% CPU in the middle of the night whenever its table stats flip the heuristics the wrong way.

> Postgres badly needs query hints like MySQL has!

https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l