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.

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)

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.

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?

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

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

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?

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

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?

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.