PostgreSQL Query Planner

This seems like the type of thing that a sophisticated query planner in 2024 would be able to figure out on its own? Maybe as a non database expert I'm expecting too much?

No. Learn to trust the query planner. It's a good thing. When you want explicitness, take a look at the explain plan.

PostgreSQL does offer insights into the plan. Just put EXPLAIN before your query.

Use EXPLAIN ANALYZE and save yourself some time. It's not that difficult to figure out what the planner is doing and tweak it. I think SQL is the best we got but I'd love to hear what you think is a better alternative.

I've had similar experiences. We're you able to generate the actual query plan in each case and see if it had been done differently?

The alternative is to write good queries! Alternatively, a better query planner that has a more liberal approach to "I know what you want, not what you're asking for". A great article on this re: Planetscale's approach is at https://vitess.io/blog/2021-11-02-why-write-new-planner/

Yes, and Postgres remains staunchly opposed to planner hints because the planner knows better! It always computes the optimal query plan!The "optimal query plan" changes at the drop of a hat, as you can see in this case. Absolutely trivial syntax changes result in a completely different query, sometimes turning a sequential scan into an index-only scan or vice versa. So, 100x difference in query time, it doesn't just do that for small tables.

SQL has EXPLAIN PLAN. SQL without EXPLAIN PLAN is useless, for exactly this reason: you have no idea how it will perform.

This is very useful! Postgres is opinionated about its lack of hints. And while I don't love hints as a development method (better to figure out why the planner made a suboptimal choice and correct the statistics accordingly), sometimes it's necessary to be sure that a query running in production isn't suddenly going to change from a fast plan to a slow one unpredictably.

If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc.Query planning is something where a poor choice can have serious performance rami