Why does a pg query stop using an index after a while?

Question

I have this query in Postgres 12.0:

SELECT "articles"."id"
FROM "articles"
WHERE ((jsonfields ->> 'etat') = '0' OR (jsonfields ->> 'etat') = '1' OR (jsonfields ->> 'etat') = '2')
ORDER BY ordre ASC;

At this time:

    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=1274.09..1274.97 rows=354 width=8) (actual time=13.000..13.608 rows=10435 loops=1)
Sort Key: ordre
Sort Method: quicksort  Memory: 874kB
->  Bitmap Heap Scan on articles  (cost=15.81..1259.10 rows=354 width=8) (actual time=1.957..10.807 rows=10435 loops=1)
Recheck Cond: (((jsonfields ->> 'etat'::text) = '1'::text) OR ((jsonfields ->> 'etat'::text) = '2'::text) OR ((jsonfields ->> 'etat'::text) = '0'::text))
Heap Blocks: exact=6839
->  BitmapOr  (cost=15.81..15.81 rows=356 width=0) (actual time=1.171..1.171 rows=0 loops=1)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.226..0.227 rows=2110 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '1'::text)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.045..0.045 rows=259 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '2'::text)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.899..0.899 rows=8066 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '0'::text)
Planning Time: 0.382 ms
Execution Time: 14.234 ms
(15 lignes)

After a while:

    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=7044.04..7079.35 rows=14127 width=8) (actual time=613.445..614.679 rows=15442 loops=1)
Sort Key: ordre
Sort Method: quicksort  Memory: 1108kB
->  Seq Scan on articles  (cost=0.00..6070.25 rows=14127 width=8) (actual time=0.060..609.477 rows=15442 loops=1)
Filter: (((jsonfields ->> 'etat'::text) = '1'::text) OR ((jsonfields ->> 'etat'::text) = '2'::text) OR ((jsonfields ->> 'etat'::text) = '3'::text))
Rows Removed by Filter: 8288
Planning Time: 0.173 ms
Execution Time: 615.744 ms
(8 lignes)

I need to re-create index:

DROP INDEX myidx;
CREATE INDEX myidx ON articles ( (jsonfields->>'etat') );

Why? How to fix this?

I tried to decrease memory for disable seqscan. It doesn't work.
I tried to do select pg_stat_reset();. It doesn't work.

Answer 1

pg_stat_reset() does not reset table statistics. It only resets counters (like how often an index was used), it has no effects on query plans.

To update table statistics, use ANALYZE (or VACUUM ANALYZE, while being at it). autovacuum should take care of this automatically, normally.

Your first query finds rows=10435, your second query finds rows=15442. Postgres expects to find rows=354 (!) in the first, but rows=14127 in the second. It largely under-estimates the number of result rows in the first, which favours indexes. So your first query was only fast by accident.

Table statistics have changed, there may be table and index bloat. Most importantly, your cost settings are probably misleading. Consider a lower setting for random_page_cost (and possibly for cpu_index_tuple_cost and others).

Related:

• Keep PostgreSQL from sometimes choosing a bad query plan

If recreating the index leads to a different query plan, the index may have been bloated. (A bloated index would also discourage Postgres from using it.) More aggressive autovacuum settings, generally or just for the table or even just the index may help.

Also, expression indexes introduce additional statistics (the essential one on jsonfields->>'etat' in your case). Dropping the index drops those, too. And the new expression index starts out with empty statistics which are filled with the next manual ANALYZE or by autovacuum. So, typically, you should run ANALYZE on the table after creating an expression index - except that in your case you currently only seem to get the fast query when based on misleading stats, so fix that first.

Maybe revisit your database design. Does that etat value really have to be nested in a JSON column? Might be a lot cheaper overall to have it as separate column.

Be that as it may, the most expensive part of your first (fast) query plan is the Bitmap Heap Scan, where Postgres reads actual data pages to return id values. A shortcut with a "covering" index would be possible since Postgres 11:

CREATE INDEX myidx ON articles ((jsonfields->>'etat')) INCLUDE (ordre, id);

But this relies on autovacuum doing its job in timely manner even more, as it requires the visibility map to be up to date.

Or, if your WHERE clause is constant (always filtering for (jsonfields ->> 'etat') = ANY ('{0,1,2}')), a partial index would reign supreme:

CREATE INDEX myidx ON articles (ordre, id)
WHERE (jsonfields ->> 'etat') = ANY ('{0,1,2}');

Answer 2

Immediately after you create the functional index, it doesn't have any statistics gathered on it, so PostgreSQL must make some generic assumptions. Once auto-analyze has had a chance to run, it has real stats to work with. Now it turns out the more-accurate estimates actually leads to a worse plan, which is rather unfortunate.

The PostgreSQL planner generally assumes much of our data is not in cache. This assumption pushes it to favor seq scans over index scan when it will be returning a large number of rows (Your second plan is returning 2/3 of the table!). The reasons it makes this assumption is that it is safer. Assuming too little data is cached leads to merely bad plans, but assuming too much is cached leads to utterly catastrophic plans.

In general, the amount of data assumed to be cache is baked into the random_page_cost setting, so you can tweak that setting if you want it. (baking it into that setting, rather than having a separate setting, was a poor design decision in my opinion, but it was made a very long time ago).

You could set random_page_cost equal to seq_page_cost, to see if that solves the problem. But that is probably not a change you would want to make permanently, as it is likely to create more problems than it solves. Perhaps the correct setting is lower than the default but still higher than seq_page_cost. You should also do EXPLAIN (ANALYZE, BUFFERS), and set track_io_timing = on, to give you more information to use in evaluating this.

Another issue is that the bitmap heap scan never needs to consult the actual JSON data. It gets all the data it needs from the index. The seq scan needs to consult the JSON data, and how slow this is will be depends on things like what type it is (json or jsonb) and how much other stuff is in that JSON. PostgreSQL rather ridiculously thinks that parsing a JSON document take about the same amount of time as comparing two integers does.

You can more or less fix this problem (for json type) by running the following statement:

update pg_proc set procost =100 where proname='json_object_field_text';

(This is imperfect, as the cost of this function gets charged to the recheck condition of the bitmap heap scan even though no recheck is done. But the recheck is charged for each tuple expected to be returned, not each tuple expected to be in the table, so this creates a distinction you can take advantage of).