Postgres changes query plan for prepared statement to something poor after multiple invocations

Question

Postgres 13.4

I have a prepared statement that is similar to this:

PREPARE my_statement AS 
SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND name_matchable = TRUE
ORDER BY name ASC, disambiguation ASC NULLS FIRST, official ASC, release_date DESC, product_code ASC, language ASC LIMIT 1

I have a BTree index on name, normalized_name, name_matchable, and a GIST index on distinct_normalized_names.

I also have an BTree index on the ordering portion of this query, mostly for purposes not related to the prepared statement name ASC, disambiguation ASC NULLS FIRST, official ASC, release_date DESC, product_code ASC, language ASC

When I prepare this statement and run EXPLAIN ANALYZE on it, I see a reasonable plan and this prepared statement executes very fast:

EXPLAIN ANALYZE EXECUTE my_statement('example');

Limit  (cost=105.26..105.26 rows=1 width=1706) (actual time=0.056..0.057 rows=0 loops=1)
  ->  Sort  (cost=105.26..105.28 rows=48 width=1706) (actual time=0.055..0.056 rows=0 loops=1)
        Sort Key: name, disambiguation NULLS FIRST, official, release_date DESC, product_code, language
        Sort Method: quicksort  Memory: 25kB
        ->  Bitmap Heap Scan on products  (cost=10.16..105.21 rows=48 width=1706) (actual time=0.043..0.044 rows=0 loops=1)
              Recheck Cond: ((normalized_name = 'example'::text) OR (distinct_normalized_names @> '{example}'::text[]))
              Filter: name_matchable
              ->  BitmapOr  (cost=10.16..10.16 rows=48 width=0) (actual time=0.042..0.042 rows=0 loops=1)
                    ->  Bitmap Index Scan on index_products_on_normalized_name  (cost=0.00..2.11 rows=20 width=0) (actual time=0.018..0.018 rows=0 loops=1)
                          Index Cond: (normalized_name = 'example'::text)
                    ->  Bitmap Index Scan on index_products_on_distinct_normalized_names_gin  (cost=0.00..8.04 rows=28 width=0) (actual time=0.023..0.023 rows=0 loops=1)
                          Index Cond: (distinct_normalized_names @> '{example}'::text[])
Planning Time: 1.624 ms
Execution Time: 0.157 ms

However, if I invoke the prepared statement around ten times or more, Postgres suddenly switches the plan to something much worse, using a field I did not WHERE-clause on and it remains there until I DEALLOCATE the prepared statement.

-- If run this statement 10 or so times, then the plan below is provided
EXPLAIN ANALYZE EXECUTE my_statement('example');

Limit  (cost=53.67..104.57 rows=1 width=1706) (actual time=763.908..763.909 rows=0 loops=1)
  ->  Incremental Sort  (cost=53.67..87248.70 rows=1713 width=1706) (actual time=763.906..763.907 rows=0 loops=1)
        Sort Key: name, disambiguation NULLS FIRST, official, release_date DESC, product_code, language
        Presorted Key: name
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
        ->  Index Scan using index_products_on_name on products  (cost=0.08..87228.90 rows=1713 width=1706) (actual time=763.888..763.888 rows=0 loops=1)
              Filter: (name_matchable AND ((normalized_name = $1) OR (distinct_normalized_names @> ARRAY[$1])))
              Rows Removed by Filter: 338960
Planning Time: 0.015 ms
Execution Time: 764.064 ms

As you can see, the total time ballooned from ~3ms to 760ms. This prepared statement is used heavily, so this isn't ideal. I can watch this problem happen in real time on a database under load by PREPAREing the statement, running it about 10 times, then seeing the performance plummet and the EXPLAIN change.

It looks like Postgres is suddenly deciding that the prepared statement should use a different index and a much different strategy, but this is worse performance.

I was able to work around this issue by not using a prepared statement at all, and I don't see the issue occur if I use raw SQL, even after repeated invocations.

Why does Postgres decide to change the plan for the statement after repeated use?

Answer 1

It thinks the generic plan you show (with the $1 in it) will be slightly faster than the custom plan (with the actual value in it), 104.57 vs 105.26. So after running a number of custom plans and thinking there is no benefit, it doesn't think it is worthwhile making a new custom plan each time. Of course the estimate is way off, but the estimate is what it has to go by.

You can set plan_cache_mode = force_custom_plan to prevent it from doing this. Of course if you set this globally, it might make other things worse, as now it will be re-planning queries all the time even when the generic plan is perfectly fine, and planning can sometimes be a bottleneck. But since you have evidence that using generic plans is actually a problem for you, and no evidence that using re-planning would be, I would just go make the change (documenting why) unless it is easy to tell your client not to prepare this particular query.