| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: missing estimation for coalesce function |
| Date: | 2019-11-28 14:51:04 |
| Message-ID: | 3524f9466c1a03e35bc0a78d9f72eaba572c0462.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Wed, 2019-11-27 at 08:47 +0100, Pavel Stehule wrote:
> 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.
>
> 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)
I think that this is asking for a planner support function:
https://www.postgresql.org/docs/current/xfunc-optimization.html
Yours,
Laurenz Albe
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andy Fan | 2019-11-28 15:21:17 | Re: Planner chose a much slower plan in hashjoin, using a large table as the inner table. |
| Previous Message | Alvaro Herrera | 2019-11-28 14:49:03 | Re: FETCH FIRST clause WITH TIES option |