Postgresql COALESCE performance problem Postgresql COALESCE performance problem postgresql postgresql

Postgresql COALESCE performance problem


Try rewriting the query of the form

SELECT *  FROM my_table WHERE value = ?   AND (? IS NULL OR id = ?)

From my own quick tests

INSERT INTO my_table select generate_series(1,50000),1;UPDATE my_table SET value = id%17;CREATE INDEX val_idx ON my_table(value);VACUUM ANALYZE my_table;\set idval 17\set pval   0explain analyze SELECT *  FROM my_table WHERE value = :pval   AND (:idval IS NULL OR id = :idval);Index Scan using my_table_pkey on my_table  (cost=0.00..8.29 rows=1 width=16) (actual time=0.034..0.035 rows=1 loops=1)   Index Cond: (id = 17)   Filter: (value = 0) Total runtime: 0.064 ms\set idval nullexplain analyze SELECT *  FROM my_table WHERE value = :pval   AND (:idval IS NULL OR id = :idval);Bitmap Heap Scan on my_table  (cost=58.59..635.62 rows=2882 width=16) (actual time=0.373..1.594 rows=2941 loops=1)   Recheck Cond: (value = 0)   ->  Bitmap Index Scan on validx  (cost=0.00..57.87 rows=2882 width=0) (actual time=0.324..0.324 rows=2941 loops=1)         Index Cond: (value = 0) Total runtime: 1.811 ms


From creating a similar table, populating it, updating statistics, and finally looking at the output of EXPLAIN ANALYZE, the only difference I see is that the first query filters like this:

Filter: ((id = COALESCE(id)) AND (value = 3))

and the second one filters like this:

Filter: (value = 3)

I see substantially different performance and execution plans when there's an index on the column "value". In the first case

Bitmap Heap Scan on my_table  (cost=19.52..552.60 rows=5 width=16) (actual time=19.311..20.679 rows=1000 loops=1)  Recheck Cond: (value = 3)  Filter: (id = COALESCE(id))  ->  Bitmap Index Scan on t2  (cost=0.00..19.52 rows=968 width=0) (actual time=19.260..19.260 rows=1000 loops=1)        Index Cond: (value = 3)Total runtime: 22.138 ms

and in the second

Bitmap Heap Scan on my_table  (cost=19.76..550.42 rows=968 width=16) (actual time=0.302..1.293 rows=1000 loops=1)  Recheck Cond: (value = 3)  ->  Bitmap Index Scan on t2  (cost=0.00..19.52 rows=968 width=0) (actual time=0.276..0.276 rows=1000 loops=1)        Index Cond: (value = 3)Total runtime: 2.174 ms

So I'd say it's slower because the db engine a) evaluates the COALESCE() expression rather than optimizing it away, and b) evaluating it involves an additional filter condition.