Re: Question about double table scans for a table

On Thu, Jul 27, 2023 at 4:49 PM Ba Jinsheng <bajinsheng(at)u(dot)nus(dot)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;