Hi
I have a report from my customer about migration his application from Oracle to Postgres.
The most significant issue was missing correct estimation for coalesce function. He had to rewrite coalesce(var, X) = X to "var IS NULL or var = X". Then the result was very satisfactory.
Example:
create table xxx(a int);
insert into xxx select null from generate_series(1,10000);
insert into xxx select 1 from generate_series(1,1000);
insert into xxx select 0 from generate_series(1,1000);
analyze xxx;
postgres=# explain analyze select * from xxx where coalesce(a, 0) = 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on xxx (cost=0.00..194.00 rows=60 width=4) (actual time=0.041..4.276 rows=11000 loops=1)
Filter: (COALESCE(a, 0) = 0)
Rows Removed by Filter: 1000
Planning Time: 0.099 ms
Execution Time: 5.412 ms
(5 rows)
postgres=# explain analyze select * from xxx where a is null or a = 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on xxx (cost=0.00..194.00 rows=10167 width=4) (actual time=0.052..5.891 rows=11000 loops=1)
Filter: ((a IS NULL) OR (a = 0))
Rows Removed by Filter: 1000
Planning Time: 0.136 ms
Execution Time: 7.522 ms
(5 rows)
I think so pattern coalesce(var, X) = X is very common so can be very interesting to support it better.
Regards
Pavel
