Horton

Re: Question about double table scans for a table

David Rowley <dgrowleyml@gmail.com>

View thread
From: David Rowley <dgrowleyml@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Ba Jinsheng <bajinsheng@u.nus.edu>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Manuel Rigger <rigger@nus.edu.sg>
Date: 2023-07-28T04:01:34Z
Lists: pgsql-bugs 
On Fri, 28 Jul 2023 at 12:12, jian he <jian.universality@gmail.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