Casting timestamp at view level to prevent type mismatch cause query plan to not undergo partition elimination and causing full table scan.

Products

• VMware Tanzu Data Suite

Issue/Introduction

(gpadmin@[local])[admin]> \d+ staging.ao_test_part1
                          Append-Only Table "staging.ao_test_part1"
 Column |              Type              | Modifiers | Storage  | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
 id     | integer                        |           | plain    |              |
 date   | timestamp(6) without time zone |           | plain    |              |
 name_  | text                           |           | extended |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Child tables: staging.ao_test_part1_1_prt_2,
              staging.ao_test_part1_1_prt_3,
              staging.ao_test_part1_1_prt_4,
              staging.ao_test_part1_1_prt_5,
              staging.ao_test_part1_1_prt_6,
              staging.ao_test_part1_1_prt_7,
              staging.ao_test_part1_1_prt_8,
              staging.ao_test_part1_1_prt_others
Distributed by: (id)
Partition by: (date)
Options: appendonly=true, compresstype=zlib, compresslevel=5
(gpadmin@[local])[admin]> \d+ staging.ao_test_part2
                  Append-Only Table "staging.ao_test_part2"
 Column |      Type      | Modifiers | Storage  | Stats target | Description
--------+----------------+-----------+----------+--------------+-------------
 id     | integer        |           | plain    |              |
 date   | timestamp9_ntz |           | plain    |              |
 name_  | text           |           | extended |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Child tables: staging.ao_test_part2_1_prt_2,
              staging.ao_test_part2_1_prt_3,
              staging.ao_test_part2_1_prt_4,
              staging.ao_test_part2_1_prt_5,
              staging.ao_test_part2_1_prt_6,
              staging.ao_test_part2_1_prt_7,
              staging.ao_test_part2_1_prt_8,
              staging.ao_test_part2_1_prt_others
Distributed by: (id)
Partition by: (date)
Options: appendonly=true, compresstype=zlib, compresslevel=5
(gpadmin@[local])[admin]> \d+ staging.ao_test_part_union_v
             View "staging.ao_test_part_union_v"
 Column |      Type      | Modifiers | Storage  | Description
--------+----------------+-----------+----------+-------------
 id     | integer        |           | plain    |
 date   | timestamp9_ntz |           | plain    |
 name_  | text           |           | extended |
View definition:
 SELECT ao_test_part1.id,
    ao_test_part1.date::timestamp9_ntz AS date,
    ao_test_part1.name_
   FROM staging.ao_test_part1
UNION ALL
 SELECT ao_test_part2.id,
    ao_test_part2.date,
    ao_test_part2.name_
   FROM staging.ao_test_part2;

 

 Gather Motion 64:1  (slice3; segments: 64)  (cost=0.00..493.18 rows=1280000 width=20)
   ->  Append  (cost=0.00..435.02 rows=20000 width=20)
         ->  Redistribute Motion 64:64  (slice1; segments: 64)  (cost=0.00..435.02 rows=12500 width=20)
               Hash Key: ao_test_part1.id
               ->  Result  (cost=0.00..435.02 rows=12500 width=20)
                     Filter: (((ao_test_part1.date)::timestamp9_ntz) = '2021-03-06 00:00:00.000000000'::timestamp9_ntz)
                     ->  Result  (cost=0.00..434.00 rows=31250 width=20)
                           ->  Sequence  (cost=0.00..431.58 rows=31250 width=20)
                                 ->  Partition Selector for ao_test_part1 (dynamic scan id: 1)  (cost=10.00..100.00 rows=2 width=4)
                                       Partitions selected: 8 (out of 8)
                                 ->  Dynamic Seq Scan on ao_test_part1 (dynamic scan id: 1)  (cost=0.00..431.58 rows=31250 width=20)
         ->  Redistribute Motion 64:64  (slice2; segments: 64)  (cost=0.00..432.25 rows=7500 width=20)
               Hash Key: ao_test_part2.id
               ->  Sequence  (cost=0.00..432.25 rows=7500 width=20)
                     ->  Partition Selector for ao_test_part2 (dynamic scan id: 2)  (cost=10.00..100.00 rows=2 width=4)
                           Partitions selected: 1 (out of 8)
                     ->  Dynamic Seq Scan on ao_test_part2 (dynamic scan id: 2)  (cost=0.00..432.25 rows=7500 width=20)
                           Filter: (date = '2021-03-06 00:00:00.000000000'::timestamp9_ntz)
 Optimizer: Pivotal Optimizer (GPORCA)

Cause

While there is an ongoing plan to fix this on the Orca side , Orca and Planner are not currently able to push down the predicate through the view due to the type mismatch.

 

 

Resolution

This is the fundamental problem. Once we add support for this, Orca should be able to push down the predicate, and subsequently perform static partition elimination.

Additional Information

As a temporary workaround, either of the following options can be used to remediate the effect through the use of partition elimination:

1) Use the same type for the column in the view (eg: either both timestamp, or both timestamp9). Then Orca and planner can perform static elimination:

2) Instead of using the view, explicitly add the predicate to each individual part of the union all statement