DBT-3 optimization

[greenlion]

Start working on the DBT-3 at scale factor 1. Problems with each query will be added to this list as comments.

This is a meta bug of sorts.

[greenlion]

Query 1

FIXED Note: Most recent check in adds support for AVG so that this query works with parallel query!

where l_shipdate <= '1998-08-26' - interval 2 day

-> Sort: lineitem.l_returnflag, lineitem.l_linestatus (actual time=13486.608..13486.609 rows=4 loops=1) -> Table scan on (actual time=0.001..0.001 rows=4 loops=1) -> Aggregate using temporary table (actual time=13486.587..13486.587 rows=4 loops=1) -> Table scan on lineitem (cost=600121.50 rows=6001215) (actual time=101.027..10571.454 rows=5898833 loops=1)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (13.50 sec)

select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= date_sub('1998-12-01', interval 97 day)
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

Problem: ECP isn't filtering rows -> Sort: lineitem.l_returnflag, lineitem.l_linestatus (actual time=15966.831..15966.832 rows=4 loops=1) -> Table scan on (actual time=0.001..0.001 rows=4 loops=1) -> Aggregate using temporary table (actual time=15966.807..15966.808 rows=4 loops=1) -> Filter: (lineitem.l_shipDATE <= (('1998-12-01' - interval 97 day))) (cost=600121.50 rows=2000205) (actual time=87.119..12979.178 rows=5902892 loops=1) -> Table scan on lineitem (cost=600121.50 rows=6001215) (actual time=87.116..12404.760 rows=6001215 loops=1)

Optimized version:

select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= '1998-08-26'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

EXPLAIN: -> Sort: lineitem.l_returnflag, lineitem.l_linestatus (actual time=15451.847..15451.848 rows=4 loops=1) -> Table scan on (actual time=0.001..0.001 rows=4 loops=1) -> Aggregate using temporary table (actual time=15451.820..15451.821 rows=4 loops=1) -> Table scan on lineitem (cost=600121.50 rows=6001215) (actual time=104.259..12324.562 rows=5902892 loops=1)

Note that the filtered number of rows is lower on the second optimized query. ECP isn't working with date_sub. Note cached evaluation in the first explain that is pushed down (not noted) in the second explain.

Also note that it is almost a FTS and so the index doesn't make any difference but it will likely in other DBT-3 queries, unless they all examine the whole lineitem table!

Turning off row visibility and transaction visibility checks shaves one second off the query. I don't think that is worth optimizing at this time. Turning off row visibility decreases count(*) query time by 2 tenths of a second (.92 with .76 without)

[greenlion]

Query 2

select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    part,
    supplier,
    partsupp,
    nation,
    region
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 14
    and p_type like '%TIN'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'AMERICA'
    and ps_supplycost = (
        select
            min(ps_supplycost)
        from
            partsupp,
            supplier,
            nation,
            region
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'AMERICA'
    )
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
LIMIT 100;```
-> Limit: 100 row(s)  (actual time=46414.522..46414.544 rows=100 loops=1)
    -> Sort: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey, limit input to 100 row(s) per chunk  (actual time=46414.521..46414.540 rows=100 loops=1)
        -> Stream results  (cost=100372.45 rows=617) (actual time=161.047..46413.755 rows=155 loops=1)
            -> Inner hash join (part.p_partkey = partsupp.ps_partkey), (partsupp.ps_supplycost = (select #2))  (cost=100372.45 rows=617) (actual time=161.043..46413.163 rows=155 loops=1)
                -> Filter: ((part.p_size = 14) and (part.p_type like '%TIN'))  (cost=39.64 rows=222) (actual time=0.040..19.192 rows=806 loops=1)
                    -> Table scan on part  (cost=39.64 rows=200000) (actual time=0.017..13.420 rows=4153 loops=1)
                -> Hash
                    -> Inner hash join (supplier.s_nationkey = nation.n_nationkey), (supplier.s_suppkey = partsupp.ps_suppkey)  (cost=25026.69 rows=2500) (actual time=78.946..105.967 rows=3432 loops=1)
                        -> Table scan on supplier  (cost=2.00 rows=10000) (actual time=4.565..29.905 rows=10000 loops=1)
                        -> Hash
                            -> Inner hash join (nation.n_regionkey = region.r_regionkey)  (cost=26.69 rows=25) (actual time=53.164..58.589 rows=83060 loops=1)
                                -> Table scan on nation  (cost=0.13 rows=25) (actual time=0.392..0.473 rows=25 loops=1)
                                -> Hash
                                    -> Inner hash join (no condition)  (cost=1.68 rows=10) (actual time=41.800..51.059 rows=16612 loops=1)
                                        -> Table scan on partsupp  (cost=0.33 rows=2) (actual time=41.117..48.776 rows=16612 loops=1)
                                        -> Hash
                                            -> Table scan on region  (cost=0.55 rows=5) (actual time=0.651..0.665 rows=1 loops=1)

Runs in 44 seconds if I turn on statistics munging and make partsupp have a row count of 2.
Query does not complete without this optimization.
Query requires bitmap index join optimization.