Re: Question about double table scans for a table

On Thu, Jul 27, 2023 at 4:49 PM Ba Jinsheng <bajinsheng@u.nus.edu> wrote:
>
> Hi everyone,
>
>
>
> Consider the query 11 in the TPC-H benchmark:
>
> select
>
>     ps_partkey,
>
>     sum(ps_supplycost * ps_availqty) as value
>
> from
>
>     PARTSUPP,
>
>     SUPPLIER,
>
>     NATION
>
> where
>
>     ps_suppkey = s_suppkey
>
>     and s_nationkey = n_nationkey
>
>     and n_name = 'MOZAMBIQUE'
>
> group by
>
>     ps_partkey
>
> having
>
>     sum(ps_supplycost * ps_availqty) > (
>
>         select
>
>             sum(ps_supplycost * ps_availqty) * 0.0001000000
>
>         from
>
>             PARTSUPP,
>
>             SUPPLIER,
>
>             NATION
>
>         where
>
>             ps_suppkey = s_suppkey
>
>             and s_nationkey = n_nationkey
>
>             and n_name = 'MOZAMBIQUE'
>
>     )
>
> order by
>
>     value desc;
>
>

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
from
    PARTSUPP,
    SUPPLIER,
    NATION
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'MOZAMBIQUE'
group by
    ps_partkey;

maybe you can use inner join like:
select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) filter (where ps_supplycost > 0
and ps_availqty > 0 ) as value
from PARTSUPP join SUPPLIER on (ps_suppkey = s_suppkey)
        join NATION on (s_nationkey = n_nationkey)
where n_name = 'MOZAMBIQUE'
group by
    ps_partkey;