> How many of us have toggled enable_seqscan to off to force an index scan? Or thrown an OFFSET 0 into a subquery to prevent the planner from flattening it?

enable_nestloop = off here.

For us, joining many complex views quickly trips the planner up, so I'm really glad to see this.

> They break on upgrades.

The irony is so does the planner. I've seen queries working perfectly fine in older PG's suddenly run away in newer versions. So hints will actually bring stability.

▲

da_chicken 4 hours ago | parent | next [-]

The planner breaking on updates is common for almost all RDBMSs. They introduce optimizations that work great for 95% of customers, and some will just have queries that now act like cardinality is way off or covering indexes are missing.

	▲	mpyne 3 hours ago \| parent [-]
		This issue was one of AWS's listed reasons for tending to prefer NoSQL style databases over "more performant" RDBMS, because of the more consistent worst-case performance, even if the result is worse average-case performance, which was important in their assumptions for scalability planning.

▲

throwatdem12311 3 hours ago | parent | prev [-]

Every single time I’ve thought I’ve needed to try these it made it worse.

Every time Claude tries to tell me to try these, it made it worse.

Not once has it made it better for me. I’m doing materialized view refreshes with a billion rows, which is small enough maybe that this doesn’t come into play…but so far the planner knows best.

If the database can’t make it fast with just smart joins and filtering then it’s the architecture that’s a problem, not the database.

Usually the only thing I need to do is increase work_mem.

	▲	silon42 2 hours ago \| parent [-]
		The hints are needed when you have a web scale / online transaction processing application, where you want to guarantee no table scan ever. Ideally, I'd want to rrun without statistics on the tables in that case (ie, no smart joins).