| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | jian he <jian(dot)universality(at)gmail(dot)com> |
| Cc: | Ba Jinsheng <bajinsheng(at)u(dot)nus(dot)edu>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Manuel Rigger <rigger(at)nus(dot)edu(dot)sg> |
| Subject: | Re: Question about double table scans for a table |
| Date: | 2023-07-28 04:01:34 |
| Message-ID: | CAApHDvqzcEeAuy8cV2xqL8uXRKGULMrvufCzCcFgoLv+EEjsWg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
On Fri, 28 Jul 2023 at 12:12, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> I think you query is equivalent to following:
>
> select
> ps_partkey,
> sum(ps_supplycost * ps_availqty) filter (where ps_supplycost > 0
> and ps_availqty > 0 ) as value
The FILTER clause is applied before aggregation. HAVING is applied
after aggregation. This is not even nearly the same.
(You might have forgotten that numbers can be negative and also you
might have missed the * 0.0001000000.)
The original query seems to want all parts apart from the ones that
are below 1/10000th of the total ps_supplycost * ps_availqty for all
parts.
David
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Asier Lostalé | 2023-07-28 05:33:51 | Re: BUG #18038: Aliases removed from view definitions |
| Previous Message | Michael Paquier | 2023-07-28 01:50:50 | Re: Fwd: BUG #18016: REINDEX TABLE failure |