[scott113341]

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard.

A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second.

The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially, the planner would sometimes wildly mis-estimate costs due to under-sampling, and would choose a bad plan. We fixed it in two different ways: 1) lower the auto-ANALYZE threshold; 2) increase the number of rows sampled when collecting statistics for the relevant column.

Again, this was “user error”. That said, it will probably happen again on the same or another query, because it’s hard to know if/when a query plan is about to change, and pg_hint_plan and similar are very heavy-handed solutions.

[williamdclt]

That's really my number 1 gripe with PG.

I'm not even too bothered by the opaqueness of the query planner (although I'd love better visibility into it). But the fact that the query plan can change any second is insane: you can't lock it, and you can't force another one as a short-term fix.

There's no option that I know of. If you reach an impossible-to-anticipate threshold and the query plan changes, your whole system can be down and you can only fix forward, which might take a _long_ time to figure out and is super dangerous as you'll pretty much have to experiment on your prod database.

It's insane, I've not yet been bit too bad by it but I know it's coming for me.

[pgaddict]

Well, what exactly would you expect for better visibility into the planner? I mean, you have the source code, and I'm not sure how to visualize the extreme number of combinations considered by the planner. Any examples of databases doing interesting things?

As for the "locking" of plans, I personally have rather serious doubts about that. Yes, I've heard it suggested as a viable solution, but knowing how vastly different plans may be "right" for the same query with just slightly different parameters ...

[williamdclt]

> what exactly would you expect for better visibility into the planner

Dunno. At the moment I need a fairly deep understanding of how the planner works (eg how it uses statistics or indexes) to optimise queries, I'd love to be able to _see_ that rather than guess. Not saying it's easy, I'm just wishing

> As for the "locking" of plans, I personally have rather serious doubts about that. Yes, I've heard it suggested as a viable solution, but knowing how vastly different plans may be "right" for the same query with just slightly different parameters ...

What's the problem with vastly different plans being "right" for the same query? All I am (and many other people are) asking for is a way to ensure PG doesn't bring down my entire system because it decided to change the query plan it uses without 1. any sort of warning 2. any way to revert it. It doesn't feel like it's asking for too much! Maybe locking plans is a good solution, maybe it's not, I'd just like _something_ that lets me sleep at night

[whoknowswhat11]

Aurora PostgreSQL has something called Query Plan Management - which I like - is meant to address this type of issue especially for large tables that have key queries that you could blow up DB basically if they go haywire in planning.

Would def be a feature that would be nice to see in PostgreSQL itself.

[cutemonster]

It'd be nice if one could tell pg to only change its query plans during certain change windows, say, the first Saturday each month, with on call staff ready.

And if there was a "Revert to o old plans" button

[citrons]

I'm currently having a similar issue where the query planner refuses to use the indexes on a search query (was fine for w hile, but one day it just started de-optimizing itself). Instead just does a seq-scan. Instead of the execution taking ~40ms with indexes the query planner thinks that the seq scan of ~1.5s is better...

Re-indexes the db and run analyze the table. It gets better for max 30min then PG de-optimizes itself again.

I'm kinda stuck on it, any ideas what can I do to resolve it?

[arp242]

Try lowering the random_page_cost value; this is the performance cost query planner uses for random reads, which is usually too high if you're using an SSD where random reads are cheap (on disks it's expensive). Just setting it to 1 works well in my case.

This solves many "it does a slow seq scan even though there's an index"-cases.

https://postgresqlco.nf/doc/en/param/random_page_cost/

There are some other query planner knobs you can tune as well; the https://postgresqlco.nf site is pretty good.

[ahachete]

If using SSD or similar fast storage subsystem, or those that hide a higher random access time vs sequential, you may indeed want to reduce random_page_cost to make random_page_cost / seq_page_cost in the 1.2-1.5 range.

But it's also wise to review the default_statistics_target being used, that autovacuum is running frequently enough (which does autoanalyze), that the analyze thresholds are also properly tuned...

Thank you for mentioning https://postgresqlco.nf Team member here :) All these parameters mentioned here are well documented there, with recommendations.

Also, have you tried the Tuning Guide? (https://postgresqlco.nf/tuning-guide)

[Erwin]

Is it a HSTORE column with GIN index? The default "FASTUPDATE=ON" option will delay updates to the index until vacuum time, but if you don't vacuum soon enough suddenly it can decide it should sequentially scan instead of reading through the delayed updates.

This is behaviour I've seen on 9.x on the Aurora variant; for that the solution was to use the FASTUPDATE=OFF index storage option. You can see the delayed tuples by using "pgstatginindex" function.

Using some of the extra options of EXPLAIN (ANALYZE, BUFFERS, COSTS) might give more hints.

If not HSTORE/GIN, then it could be that the analyzer, after some auto-analyze of the table things that what you are asking for will match a significant number of the rows in the table. So there's no point in random seeking through an index because it thinks it needs to read e.g. 50% of the table anyway, so it might just as well not use the index.

[adeel_siddiqui]

set `enable_seqscan` = 'off' or set local `enable_seqscan` = 'off'. This will force the pg query planner to use indexes. Experiment with it until you figure out why your query performance deteriorates. Maybe you are doing a lot of updates/deletes? Increase the statistics sampling size? Autovacuum more frequently?

[silon42]

Could one disable statistics completely? Personally, I'd prefer to specify the execution plan manually.

[jashmatthews]

Tune autovacuum analyze to run every 30mins. Seriously. The query planner needs up to date statistics.

[Dylan16807]

Why does it need up to date statistics to decide not to change anything?

I mean, if you could freeze statistics entirely wouldn't that fix this problem?

[jashmatthews]

Because the contents of the table is changing the statistics are becoming out of date.

[Dylan16807]

That doesn't answer the question at all.

The old statistics said to use the index.

If it's still using old statistics, why does the behavior change?

[gmokki]

Because the user is using values that are no longer covered by the statistics. For example incrementing timestamp or id column. If the stats are from yesterday and they say nothing about the frequency of todays timestamps the query will have to take a pessimistic view of the world. It might be that the data has radically changed since the last stats run, or not. Need to analyze the table to know and make optimal choices.

[pgaddict]

I'm not quite sure why you consider this "user error"? I work on the optimizer a bit, and I wouldn't say it's a fault of the user ...

OTOH I'm not sure it's a fault of the DB either :-( The statistics collected by ANALYZE are pretty much a lossy compressed version of the database, and so some details are missing - that's kinda the point of collecting the stats.

I'm not sure why lowering the autoanalyze threshold would fix this - it increases the frequency of stats updates, so my feeling is it makes it more likely to trigger similar issue. OTOH increasing the statistics target seems like the right thing to do (although it also keeps more accurate stats, not just increase the sample size).

I don't know if there are better solutions (both practical and in principle) :-(

[da_chicken]

Unfortunately, this is just the reality of using an RDBMS. I've seen similar behavior on Informix and SQL Server (with a smaller load than yours). They all occasionally generate suboptimal query plans. That's what your DBA is for.

SQL Server was somewhat notorious for it when migrating to 2014 because they rewrote the cardinality estimator. It generally worked better, but in some systems it really didn't. Some people ended up using a trace flag to use the legacy estimator. They have steadily improved the new estimator and it's no longer a problem, but it goes to show how much is going on under the surface.

[rrrrrrrrrrrryan]

> this is just the reality of using an RDBMS

It's the reality of Postgres, yes, but not all relational database. You mentioned SQL Server, which lets you lock in a query plan, specifically to cover the use case the parent described. When you have a Very Important frequently-run query that pulls from a monstrous table, it's nice to be able to sleep peacefully knowing the DB won't shit the bed because something completely unrelated changed someplace else in the database.

One fair criticism of Postgres (and many other open source projects) is that they can be a little too religious about how the thing should work in an ideal world (in this case, SQL being as declarative as possible), sometimes to the detriment of practicality and of making things easier for the business.

[zz865]

We update statistics weekly on SQL server. One week did did a particularly aggressive data cleanup and then ran stats which created a bad plan when the "tiny" table quickly grew.

[masklinn]

> Unfortunately, this is just the reality of using an RDBMS. I've seen similar behavior on Informix and SQL Server (with a smaller load than yours). They all occasionally generate suboptimal query plans. That's what your DBA is for.

Other DBs let you lock in query plans or provide query hints, but postgres' developers are against either, which is not necessarily complete nonsense as it avoids users shooting themselves in the foot… but it also prevents users from digging themselves out of query planner stupidity.