View Folding and dynamic partition elimination

Database
Highlighted
Enthusiast

View Folding and dynamic partition elimination

Hi all,

 

TD15.10: it turns out view folding has its limits - "view with aggregation that is joined to another table must be spooled."

Let's take a simple example:

 

 

CREATE MULTISET TABLE tgo_tb1
(
  cola integer
, colb integer
, colc date
)
PRIMARY INDEX (cola)
PARTITION BY ( RANGE_N(colc  BETWEEN '2018-01-01' AND '2030-12-31' EACH INTERVAL '1' DAY ,
 NO RANGE));

 
CREATE SET TABLE tgo_tb_cal
( dat_cal DATE
, mthid INTEGER)
PRIMARY INDEX (dat_cal)
;

REPLACE VIEW tgo_v_tb1
AS
SELECT 
  cola
, colc
, SUM(colb) AS sum_colb
FROM tgo_tb1
GROUP BY 1,2
;

 

The view tgo_v_tb1 will never benefit from DPE, unless the partitioning filter is in the WHERE clause. If one joins, for instance, a calendar table to the aggregate view, no DPE will take place, because the whole view will be spooled first.

 

Explain   sel * from tgo_v_tb1 a
  inner join tgo_tb_cal b
  on a.colc = b.dat_cal
  where b.mthid=201805;

  1) First, we lock b for read on a reserved RowHash to prevent
     global deadlock.
  2) Next, we lock tgo_tb1 in view tgo_v_tb1 for read on a
     reserved RowHash in all partitions to prevent global deadlock.
  3) We lock b for read, and we lock tgo_tb1 in view
     tgo_v_tb1 for read.
  4) We do an all-AMPs SUM step to aggregate from tgo_tb1 in
     view tgo_v_tb1 by way of an all-rows scan with a condition of (
     "NOT (tgo_tb1 in view tgo_v_tb1.colc IS NULL)")
     , grouping by field1 ( tgo_tb1.cola ,tgo_tb1.colc).
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 3.  The size of Spool 3 is estimated with high confidence
     to be 33,365,051 rows (1,367,967,091 bytes).  The estimated time
     for this step is 0.40 seconds.
  5) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
          way of an all-rows scan into Spool 1 (used to materialize
          view, derived table, table function or table operator a)
          (all_amps), which is built locally on the AMPs.  The size of
          Spool 1 is estimated with high confidence to be 33,365,051
          rows (1,101,046,683 bytes).  The estimated time for this step
          is 0.17 seconds.
       2) We do an all-AMPs RETRIEVE step from b by way of an
          all-rows scan with a condition of ("(NOT (b.dat_cal IS
          NULL )) AND (b.mthid = 201805)") into Spool 6
          (all_amps), which is duplicated on all AMPs.  The size of
          Spool 6 is estimated with low confidence to be 20,088 rows (
          421,848 bytes).  The estimated time for this step is 0.02
          seconds.
  6) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an
     all-rows scan, which is joined to Spool 1 (Last Use) by way of an
     all-rows scan.  Spool 6 and Spool 1 are joined using a dynamic
     hash join, with a join condition of ("COLC = dat_cal").  The
     result goes into Spool 5 (group_amps), which is built locally on
     the AMPs.  The size of Spool 5 is estimated with low confidence to
     be 1,665,567 rows (121,586,391 bytes).  The estimated time for
     this step is 0.09 seconds.
  7) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 5 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.66 seconds.

Running the query directly on the table will encourage the optimiser to choose DPE, of course:

Explain   SELECT cola, colc, SUM(colb) AS sum_colb
 FROM tgo_tb1 a
   inner join tgo_tb_cal b
  on a.colc = b.dat_cal
  where b.mthid=201805
 GROUP BY 1,2;

  1) First, we lock b for read on a reserved RowHash to prevent
     global deadlock.
  2) Next, we lock a for read on a reserved RowHash in all
     partitions to prevent global deadlock.
  3) We lock b for read, and we lock a for read.
  4) We do an all-AMPs RETRIEVE step from b by way of an
     all-rows scan with a condition of ("(NOT (b.dat_cal IS NULL
     )) AND (b.mthid = 201805)") into Spool 4 (all_amps), which
     is duplicated on all AMPs.  Then we do a SORT to partition by
     rowkey.  The size of Spool 4 is estimated with low confidence to
     be 20,088 rows (341,496 bytes).  The estimated time for this step
     is 0.02 seconds.
  5) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
     all-rows scan, which is joined to a with no residual
     conditions.  Spool 4 and a are joined using a dynamic hash
     join, with a join condition of ("a.colc = dat_cal")
     enhanced by dynamic partition elimination.  The result goes into
     Spool 3 (all_amps), which is built locally on the AMPs.  The size
     of Spool 3 is estimated with low confidence to be 9,969,210 rows (
     269,168,670 bytes).  The estimated time for this step is 0.08
     seconds.
  6) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 ( a.cola
     ,a.colc).  Aggregate Intermediate Results are computed
     locally, then placed in Spool 5.  The size of Spool 5 is estimated
     with low confidence to be 9,969,210 rows (408,737,610 bytes).  The
     estimated time for this step is 0.07 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 1 (group_amps), which is built locally
     on the AMPs.  The size of Spool 1 is estimated with low confidence
     to be 9,969,210 rows (488,491,290 bytes).  The estimated time for
     this step is 0.05 seconds.
  8) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.22 seconds.

I know this works as designed, but does anyone know whether it is going to be redesigned soon? If one builds a layer of aggregation views, it makes the partitioning of tables kind of obsolete. Same goes for partitioned AJIs, they just get spooled at the beginning of the execution plan with no DPE in place.

 

Does any of you guys have a functioning workaround to make DPE work under these circumnstances? Adding a join on the calendar table to the aggregate view is not an option, as there are many columns in the calendar table that users might want to filter on.