From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | missing estimation for coalesce function |
Date: | 2019-11-27 07:47:56 |
Message-ID: | CAFj8pRAyD-htB9wFqT55gXMXxLhCT3zoLAd-y72EoN7EkbctRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-11-27 08:01:47 | Re: pglz performance |
Previous Message | Etsuro Fujita | 2019-11-27 07:30:49 | Re: Problem while updating a foreign table pointing to a partitioned table on foreign server |