optimizer items

[zhouqingqing]

• MEMO

  • better visualizer
  • branch and bound pruning
  • aggregation/ctes: by generate multiple memo
  • parallel ~~make it queryable with SQL/DataSet interface~~

• CE

  • complicated filter selectivity
  • CE of aggrs, etc
  • Special: FK/PK join recognization
  • more sophisticated modeling (ref. PostgreSQL)

• Others

  • get a clear cut between optimizer and parser
  • partition elimination
  • eager/lazy aggregations
  • AQP

Aggregation: select count() from lineitem, partsupp where l_partkey=ps_suppkey group by ps_availqty>100; select sum(c) from lineitem, (select ps_suppkey, ps_availqty>100, count() from partsupp group by ps_suppkey, ps_availqty>100) ps(ps_suppkey, ps_availqty100, c) where ps_suppkey=l_partkey group by ps_availqty100;

23294
226
Total cost: 25672, memory=368
PhysicHashAgg 1403_1445  (inccost=25672, cost=6007, rows=1, memory=8) (actual rows=2)
    Output: {sum(c)}[1]
    Aggregates: sum(c[0])
    Group by: ps_availqty100[1]
    -> PhysicHashJoin 1431_1447  (inccost=19665, cost=12030, rows=6005, memory=180) (actual rows=494)
        Output: c[0],ps_availqty100[1]
        Filter: ps_suppkey[2]=l_partkey[3]
        -> PhysicFromQuery 1438_1450 <ps> (inccost=1630, cost=10, rows=10) (actual rows=17)
            Output: c[2],ps_availqty100[1],ps_suppkey[0]
            -> PhysicHashAgg 1441_1452  (inccost=1620, cost=820, rows=10, memory=180) (actual rows=17)
                Output: {ps_suppkey}[0],{ps_availqty>100}[1],{count(*)(0)}[2]
                Aggregates: count(*)(0)
                Group by: ps_suppkey[0], {ps_availqty>100}[1]
                -> PhysicScanTable 1443_1454 partsupp (inccost=800, cost=800, rows=800) (actual rows=800)
                    Output: ps_suppkey[1],ps_availqty[2]>100,ps_availqty[2]
        -> PhysicScanTable 1444_1458 lineitem (inccost=6005, cost=6005, rows=6005) (actual rows=6005)
            Output: l_partkey[1]

-- profiling plan --
23294
226
Total cost: 975212, memory=12808
PhysicHashAgg 1370_1406  (inccost=975212, cost=480402, rows=1, memory=8) (actual rows=2)
    Output: {count(*)(0)}[1]
    Aggregates: count(*)(0)
    Group by: ps_availqty[0]>100
    -> PhysicHashJoin 1398_1408  (inccost=494810, cost=488005, rows=480400, memory=12800) (actual rows=23520)
        Output: ps_availqty[0]
        Filter: l_partkey[2]=ps_suppkey[1]
        -> PhysicScanTable 1404_1411 partsupp (inccost=800, cost=800, rows=800) (actual rows=800)
            Output: ps_availqty[2],ps_suppkey[1]
        -> PhysicScanTable 1405_1413 lineitem (inccost=6005, cost=6005, rows=6005) (actual rows=6005)
            Output: l_partkey[1]