Pivotal Knowledge Base

Follow

Query Performance of Common Table Expressions (CTEs) are Slower after Upgrading to Greenplum 4.3.15

Environment

  • Pivotal Greenplum Database (GPDB) 4.3.15 and above
  • Using the legacy optimizer

Symptom

After upgrading to a version of Greenplum 4.3.15 or above, while using the legacy optimizer, some queries that use Common Table Expressions (CTEs) are much slower than the previous versions of Greenplum. The query plan cost is much higher as well.

This issue is not seen while using the Pivotal Optimizer/GPORCA.  

Cause

Greenplum 4.3.15 and above inline simple CTEs. A simple CTE is defined as a CTE that does not contain another CTE, is not correlated, and is not referenced more than once.

The GUC gp_inline_simple_cte controls CTE inlining which is ON by default.

Depending on how the query plan is created this can generate better or poorer performance.

The following illustrates this behavior.

Plan #1 - 4.3.14.0

Here we can see that the subquery scan of table foo shrinks the nested loop width from 5684 to 148.

                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Gather Motion 3:1  (slice2; segments: 3)  (cost=1.00..5.64 rows=4 width=148)
  ->  Subquery Scan foo (cost=1.00..5.64 rows=2 width=148)
        ->  Nested Loop  (cost=1.00..5.38 rows=2 width=5684)
              ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..2.14 rows=38 width=0)
                    ->  Seq Scan on test_ext  (cost=0.00..1.01 rows=1 width=0)
              ->  Materialize  (cost=1.00..1.01 rows=1 width=5684)
                    ->  Seq Scan on test_cte  (cost=0.00..1.00 rows=1 width=5684)
Optimizer status: legacy query optimizer
(9 rows)

Plan #2 - 4.3.16.1

Here we can see there is no subquery scan as the nested loop contains the required columns only as the CTE was processed inline so final width is 148.

                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 3:1  (slice2; segments: 3)  (cost=1.00..5.64 rows=4 width=148)
  ->  Nested Loop  (cost=1.00..5.64 rows=2 width=148)
        ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..2.14 rows=38 width=0)
              ->  Seq Scan on test_ext  (cost=0.00..1.01 rows=1 width=0)
        ->  Materialize  (cost=1.00..1.01 rows=1 width=148)
              ->  Seq Scan on test_cte  (cost=0.00..1.00 rows=1 width=148)
Optimizer status: legacy query optimizer

If we turn off gp_inline_simple_cte we revert back to the same plan as 4.3.14 and older:

Plan #3 - 4.3.16.1 with gp_inline_simple_cte OFF

 gp_inline_simple_cte
----------------------
 off
(1 row)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Gather Motion 3:1  (slice2; segments: 3)  (cost=1.00..5.64 rows=4 width=148)
  ->  Subquery Scan foo  (cost=1.00..5.64 rows=2 width=148)
        ->  Nested Loop  (cost=1.00..5.38 rows=2 width=5684)
              ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..2.14 rows=38 width=0)
                    ->  Seq Scan on test_ext  (cost=0.00..1.01 rows=1 width=0)
              ->  Materialize  (cost=1.00..1.01 rows=1 width=5684)
                    ->  Seq Scan on test_cte  (cost=0.00..1.00 rows=1 width=5684)
Optimizer status: legacy query optimizer
(9 rows)
```

This behaviour can also cause poor execution plans to be created.  For example here we can see a massive cost being generated due to the Nested Loop node being created due to the inlined CTE:

Plan #4 - 4.3.16.1 with gp_inline_simple_cte ON

Gather Motion 56:1  (slice4; segments: 56)  (cost=385340934803668.688..385340935041015.500 rows=1276856 width=792)
  ->  HashAggregate  (cost=385340934803668.688..385340935041015.500 rows=22801 width=792)
          Group By: "?column1?", "?column2?", "?column3?", "?column4?", "?column5?", "?column3?", "?column7?", "?column8?", "?column9?", "?column3?" "?column11?", "?column12?", "?column13?", "?column14?", "?column15?", "?column16?", "?column17?
        ->  Redistribute Motion 56:56  (slice3; segments: 56)  (cost=385340933646985.500..385340934566321.875 rows=22801 width=616)
                Hash Key: attr_1, attr_2, attr_1, unnamed_attr_4, unnamed_attr_5, attr_1, unnamed_attr_7, unnamed_attr_8, unnamed_attr_9, attr_1, unnamed_attr_11, unnamed_attr_1, unnamed_attr_13, unnamed_attr_14, unnamed_attr_15, unnamed_attr_15, unnamed_attr_17
              ->  HashAggregate  (cost=385340933646985.500..385340934540784.750 rows=22801 width=616)
                      Group By: 'R'::bpchar, CASE WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 6::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '6 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '6 days'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 5::double precision AND date_trunc('day'::text, a.src_rec_creat_dt) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) AND a.src_rec_creat_dt < to_timestamp(to_char(a.src_rec_creat_dt, 'yyyymmdd'::text) || '12'::text, 'yyyymmddhh24'::text) THEN date_trunc('day'::text, a.src_rec_creat_dt) WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 5::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '7 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '7 days'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 4::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '1 day'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '1 day'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 3::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '2 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '2 days'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 2::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '3 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '3 days'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 1::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '4 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '4 days'::interval WHEN date_part('dow'::text, date_trunc('day'::text, a.src_rec_creat_dt)) = 0::double precision AND (date_trunc('day'::text, a.src_rec_creat_dt) + '5 days'::interval) <= (date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval) THEN date_trunc('day'::text, a.src_rec_creat_dt) + '5 days'::interval ELSE date_trunc('MONTH'::text, date_trunc('day'::text, a.src_rec_creat_dt)) + '1 mon -1 days'::interval END, a.date_filled, btrim(btrim(a.pbm_clm_nbr::text, ' '::text), ''::text), a.claim_type_cd::character(1), grgr.grgr_ck, COALESCE(a.facets_cls_key, '0001'::character varying), COALESCE(a.facets_cls_pln_key, 'RX'::character varying), COALESCE(a.facets_prod_cd, 'RX'::character varying), a.pharmacy_nbr, 'L'::bpchar, 'R'::bpchar, ' '::bpchar, CASE WHEN a.pharm_network_flag IS NULL THEN 'U'::character varying WHEN a.pharm_network_flag::text = 'Y'::text THEN 'P'::character varying ELSE a.pharm_network_flag END, btrim(COALESCE(CASE WHEN grgr.cici_id = 'WA'::bpchar THEN '932'::bpchar WHEN grgr.cici_id = 'OR'::bpchar THEN '851'::bpchar WHEN grgr.cici_id = 'ID'::bpchar THEN '611'::bpchar WHEN grgr.cici_id = 'UT'::bpchar THEN '410'::bpchar WHEN grgr.cici_id = 'BR'::bpchar THEN 'BR'::bpchar WHEN grgr.cici_id = 'OM'::bpchar THEN 'OM'::bpchar WHEN grgr.cici_id = 'OF'::bpchar THEN 'OF'::bpchar WHEN grgr.cici_id = 'AS'::bpchar THEN 'AS'::bpchar ELSE clmi.clmi_host_plan_cd END, ''::bpchar)::text), btrim(COALESCE(CASE WHEN grgr.cici_id = 'WA'::bpchar THEN '932'::bpchar WHEN grgr.cici_id = 'OR'::bpchar THEN '851'::bpchar WHEN grgr.cici_id = 'ID'::bpchar THEN '611'::bpchar WHEN grgr.cici_id = 'UT'::bpchar THEN '410'::bpchar WHEN grgr.cici_id = 'BR'::bpchar THEN 'BR'::bpchar WHEN grgr.cici_id = 'OM'::bpchar THEN 'OM'::bpchar WHEN grgr.cici_id = 'OF'::bpchar THEN 'OF'::bpchar WHEN grgr.cici_id = 'AS'::bpchar THEN 'AS'::bpchar ELSE clmi.clmi_host_plan_cd END, ''::bpchar)::text), btrim(COALESCE("substring"(clmi.clmi_its_sbsb_id::text, 1, 3), ''::text), ''::text)
                    ->  Nested Loop Left Join  (cost=186329309.360..385340933509723.438 rows=22802 width=149)
                            Join Filter: false
                          ->  Hash Join  (cost=87242.280..74647158.960 rows=22802 width=115)
                                  Hash Cond: quebec_three.four_golf::bpchar = three.yankee
                                ->  Seq Scan on tango quebec_three  (cost=0.000..74364821.920 rows=25474 width=117)
                                        Filter: CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra'::interval ELSE papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END >= 'whiskey'::timestamp without time zone AND CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra'::interval) <= (papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra'::interval ELSE papa_five('two'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END < 'mike_yankee'::timestamp without time zone
                                ->  Hash  (cost=42543.080..42543.080 rows=63856 width=16)
                                      ->  Broadcast Motion 56:56  (slice1; segments: 56)  (cost=2992.760..42543.080 rows=63856 width=16)
                                            ->  Hash Left Join  (cost=2992.760..6145.160 rows=1141 width=16)
                                                    Hash Cond: three.november_six = three.november_six
                                                  ->  Seq Scan on alpha_kilo three  (cost=0.000..2194.560 rows=1141 width=13)
                                                  ->  Hash  (cost=2194.560..2194.560 rows=1141 width=7)
                                                        ->  Seq Scan on alpha_kilo three  (cost=0.000..2194.560 rows=1141 width=7)
                          ->  Materialize  (cost=186242067.080..350561098.880 rows=245481280 width=34)
                                ->  Broadcast Motion 56:56  (slice2; segments: 56)  (cost=0.000..145645600.400 rows=245481280 width=34)
                                      ->  Seq Scan on delta quebec_victor  (cost=0.000..5721270.800 rows=4383595 width=34)

While turning gp_inline_simple_cte OFF off we have a much lower cost as the CTE is not inlined and avoids generating a nested loop as we move up the plan:

Plan #5 - 4.3.16.1 with gp_inline_simple_cte OFF

Gather Motion 56:1  (slice5; segments: 56)  (cost=87700776.430..88005860.610 rows=1284565 width=813)
    Rows out:  1760469 rows at destination with 16731 ms to first row, 17340 ms to end, start offset by 2044 ms.
  ->  GroupAggregate  (cost=87700776.430..88005860.610 rows=22939 width=813)
          Group By:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
          Rows out:  Avg 31436.9 rows x 56 workers.  Max 31821 rows (seg16) with 16666 ms to first row, 16906 ms to end, start offset by 2114 ms.
          Executor memory:  8K bytes avg, 8K bytes max (seg0).
        ->  Sort  (cost=87700776.430..87703987.840 rows=22939 width=637)
                Sort Key: quebec_three.victor_hotel, quebec_three.two_echo, quebec_three.five, uniform_romeo4, quebec_three.quebec_quebec, quebec_three.november_six, quebec_three.charlie_oscar, quebec_three.zulu, quebec_three.mike_india, uniform_romeo10, quebec_three.echo_romeo, quebec_three.oscar_foxtrot, quebec_three.charlie_three, quebec_three.yankee_echo, uniform_romeo15, uniform_romeo16, uniform_romeo17
                Rows out:  Avg 31436.9 rows x 56 workers.  Max 31821 rows (seg16) with 16666 ms to first row, 16671 ms to end, start offset by 2114 ms.
                Executor memory:  17145K bytes avg, 17145K bytes max (seg0).
                Work_mem used:  17145K bytes avg, 17145K bytes max (seg0). Workfile: (0 spilling, 0 reused)
              ->  Redistribute Motion 56:56  (slice4; segments: 56)  (cost=86863928.240..87570439.010 rows=22939 width=637)
                      Hash Key:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                      Rows out:  Avg 31436.9 rows x 56 workers at destination.  Max 31821 rows (seg16) with 15873 ms to first row, 16603 ms to end, start offset by 2114 ms.
                    ->  GroupAggregate  (cost=86863928.240..87544747.710 rows=22939 width=637)
                            Group By:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                            Rows out:  Avg 31436.9 rows x 56 workers.  Max 31922 rows (seg4) with 15919 ms to first row, 16076 ms to end, start offset by 2088 ms.
                            Executor memory:  8K bytes avg, 8K bytes max (seg0).
                          ->  Sort  (cost=86863928.240..86867139.650 rows=22939 width=434)
                                  Sort Key: quebec_three.victor_hotel, quebec_three.two_echo, quebec_three.five, "november_zulu", quebec_three.quebec_quebec, quebec_three.november_six, quebec_three.charlie_oscar, quebec_three.zulu, quebec_three.mike_india, "oscar_romeo", quebec_three.echo_romeo, quebec_three.oscar_foxtrot, quebec_three.charlie_three, quebec_three.yankee_echo, "sierra_six", "echo_six", "?victor_juliet35?
                                  Rows out:  Avg 31436.9 rows x 56 workers.  Max 31922 rows (seg4) with 15919 ms to first row, 15923 ms to end, start offset by 2088 ms.
                                  Executor memory:  17145K bytes avg, 17145K bytes max (seg0).
                                  Work_mem used:  17145K bytes avg, 17145K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                                ->  Hash Left Join  (cost=10200755.040..86733590.800 rows=22939 width=434)
                                        Hash Cond: quebec_three.uniform_charlie::bpchar = quebec_victor.uniform_charlie
                                        Join Filter: quebec_three.echo_romeo = ANY ('bravo'::bpchar[])
                                        Rows out:  Avg 31436.9 rows x 56 workers.  Max 31922 rows (seg4) with 6049 ms to first row, 15857 ms to end, start offset by 2088 ms.
                                        Executor memory:  25246K bytes avg, 25309K bytes max (seg25).
                                        Work_mem used:  25246K bytes avg, 25309K bytes max (seg25). Workfile: (56 spilling, 0 reused)
                                        Work_mem wanted: 402459K bytes avg, 402804K bytes max (seg20) to lessen workfile I/O affecting 56 workers.
                                        (seg20)  Initial batch 0:
                                        (seg20)    Wrote 326128K bytes to inner workfile.
                                        (seg20)    Wrote 5552K bytes to outer workfile.
                                        (seg20)  Initial batches 1..15:
                                        (seg20)    Read 326145K bytes from inner workfile: 21743K avg x 15 nonempty batches, 21807K max.
                                        (seg20)    Read 5657K bytes from outer workfile: 378K avg x 15 nonempty batches, 392K max.
                                        (seg20)  Hash chain length 4.5 avg, 18 max, using 1036527 of 1048624 buckets.
                                      ->  Redistribute Motion 56:56  (slice3; segments: 56)  (cost=177683.760..75919235.230 rows=22939 width=392)
                                              Hash Key: tango::bpchar
                                              Rows out:  Avg 31436.9 rows x 56 workers at destination.  Max 31922 rows (seg4) with 0.067 ms to first row, 8678 ms to end, start offset by 8137 ms.
                                            ->  Hash Left Join  (cost=177683.760..75893543.930 rows=22939 width=392)
                                                    Hash Cond: quebec_three.november_six = three.november_six
                                                    Rows out:  Avg 31436.9 rows x 56 workers.  Max 31492 rows (seg47) with 131 ms to first row, 13355 ms to end, start offset by 2087 ms.
                                                    Executor memory:  2134K bytes avg, 2134K bytes max (seg0).
                                                    Work_mem used:  2134K bytes avg, 2134K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                                                    (seg47)  Hash chain length 1.0 avg, 1 max, using 68260 of 131111 buckets.
                                                  ->  Hash Join  (cost=88841.880..75608805.850 rows=22939 width=112)
                                                          Hash Cond: quebec_three.four_golf::bpchar = three.yankee_romeo
                                                          Rows out:  Avg 31436.9 rows x 56 workers.  Max 31492 rows (seg47) with 75 ms to first row, 13277 ms to end, start offset by 2143 ms.
                                                          Executor memory:  2667K bytes avg, 2667K bytes max (seg0).
                                                          Work_mem used:  2667K bytes avg, 2667K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                                                          (seg47)  Hash chain length 1.3 avg, 6 max, using 53314 of 131111 buckets.
                                                        ->  Seq Scan on tango quebec_three  (cost=0.000..74983289.640 rows=25570 width=117)
                                                                Filter: CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform_kilo'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra_lima'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra_lima'::interval ELSE papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END >= 'whiskey'::timestamp without time zone AND CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform_kilo'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra_lima'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra_lima'::interval ELSE papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END < 'mike_yankee'::timestamp without time zone
                                                                Rows out:  Avg 31436.9 rows x 56 workers.  Max 31492 rows (seg47) with 15 ms to first row, 13030 ms to end, start offset by 2203 ms.
                                                        ->  Hash  (cost=41297.180..41297.180 rows=67921 width=13)
                                                                Rows in:  Avg 68260.0 rows x 56 workers.  Max 68260 rows (seg0) with 33 ms to end, start offset by 2151 ms.
                                                              ->  Broadcast Motion 56:56  (slice1; segments: 56)  (cost=0.000..41297.180 rows=67921 width=13)
                                                                      Rows out:  Avg 68260.0 rows x 56 workers at destination.  Max 68260 rows (seg0) with 0.119 ms to first row, 17 ms to end, start offset by 2151 ms.
                                                                    ->  Seq Scan on alpha_kilo three  (cost=0.000..2582.210 rows=1213 width=13)
                                                                            Rows out:  Avg 1218.9 rows x 56 workers.  Max 1310 rows (seg25) with 0.128 ms to first row, 0.999 ms to end, start offset by 2102 ms.
                                                  ->  Hash  (cost=41297.180..41297.180 rows=67921 width=7)
                                                          Rows in:  Avg 68260.0 rows x 56 workers.  Max 68260 rows (seg0) with 38 ms to end, start offset by 2112 ms.
                                                        ->  Broadcast Motion 56:56  (slice2; segments: 56)  (cost=0.000..41297.180 rows=67921 width=7)
                                                                Rows out:  Avg 68260.0 rows x 56 workers at destination.  Max 68260 rows (seg0) with 0.157 ms to first row, 22 ms to end, start offset by 2112 ms.
                                                              ->  Seq Scan on alpha_kilo three  (cost=0.000..2582.210 rows=1213 width=7)
                                                                      Rows out:  Avg 1218.9 rows x 56 workers.  Max 1310 rows (seg25) with 0.250 ms to first row, 1.511 ms to end, start offset by 2102 ms.
                                      ->  Hash  (cost=6107215.680..6107215.680 rows=4680014 width=55)
                                              Rows in:  Avg 292656.1 rows x 56 workers.  Max 294492 rows (seg49) with 5887 ms to end, start offset by 2096 ms.
                                            ->  Seq Scan on delta quebec_victor  (cost=0.000..6107215.680 rows=4680014 width=55)
                                                    Rows out:  Avg 4683158.1 rows x 56 workers.  Max 4687167 rows (seg20) with 0.518 ms to first row, 3022 ms to end, start offset by 2092 ms.

While using GPORCA/Pivotal Optimizer (ie. setting GUC optimizer=ON) we see the best plan over the legacy planner with or without gp_inline_simple_cte ON:

Plan #6 - 4.3.16.1 with optimizer ON

Gather Motion 56:1  (slice6; segments: 56)  (cost=0.000..13684.650 rows=2 width=324)
    Rows out:  5250 rows at destination with 15562 ms to first row, 15565 ms to end, start offset by 3213 ms.
  ->  Result  (cost=0.000..13684.640 rows=1 width=324)
          Rows out:  Avg 93.8 rows x 56 workers.  Max 115 rows (seg32) with 15548 ms to first row, 15549 ms to end, start offset by 3227 ms.
        ->  GroupAggregate  (cost=0.000..13684.640 rows=1 width=340)
                Group By:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                Rows out:  Avg 93.8 rows x 56 workers.  Max 115 rows (seg32) with 15548 ms to first row, 15549 ms to end, start offset by 3227 ms.
                Executor memory:  8K bytes avg, 8K bytes max (seg0).
              ->  Sort  (cost=0.000..13684.640 rows=1 width=340)
                      Sort Key: victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                      Rows out:  Avg 99.5 rows x 56 workers.  Max 124 rows (seg50) with 15521 ms to end, start offset by 3254 ms.
                      Executor memory:  145K bytes avg, 145K bytes max (seg0).
                      Work_mem used:  145K bytes avg, 145K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                    ->  Redistribute Motion 56:56  (slice5; segments: 56)  (cost=0.000..13684.640 rows=1 width=340)
                            Hash Key:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                            Rows out:  Avg 99.5 rows x 56 workers at destination.  Max 124 rows (seg50) with 15518 ms to first row, 15520 ms to end, start offset by 3255 ms.
                          ->  Result  (cost=0.000..13684.640 rows=1 width=340)
                                  Rows out:  Avg 99.5 rows x 56 workers.  Max 120 rows (seg33) with 15542 ms to first row, 15543 ms to end, start offset by 3230 ms.
                                ->  GroupAggregate  (cost=0.000..13684.640 rows=1 width=340)
                                        Group By:victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                                        Rows out:  Avg 99.5 rows x 56 workers.  Max 120 rows (seg33) with 15542 ms to first row, 15543 ms to end, start offset by 3230 ms.
                                        Executor memory:  8K bytes avg, 8K bytes max (seg0).
                                      ->  Sort  (cost=0.000..13684.640 rows=1 width=192)
                                              Sort Key: victor, two_echo, tango.two_golf, romeo_kilo, quebec_quebec_three, sierra_bravo.alpha_kilo.november_six, charlie_oscar, zulu, mike_india, foxtrot, echo_romeo, oscar_foxtrot, charlie_three, yankee_echo, quebec_quebec_zulu, uniform_hotel, mike_kilo
                                              Rows out:  Avg 99.5 rows x 56 workers.  Max 120 rows (seg33) with 15542 ms to end, start offset by 3230 ms.
                                              Executor memory:  81K bytes avg, 81K bytes max (seg0).
                                              Work_mem used:  81K bytes avg, 81K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                                            ->  Result  (cost=0.000..13684.640 rows=1 width=192)
                                                    Rows out:  Avg 99.5 rows x 56 workers.  Max 120 rows (seg33) with 15280 ms to first row, 15541 ms to end, start offset by 3231 ms.
                                                  ->  Redistribute Motion 56:56  (slice4; segments: 56)  (cost=0.000..13684.640 rows=1 width=218)
                                                          Rows out:  Avg 99.5 rows x 56 workers at destination.  Max 120 rows (seg33) with 15280 ms to first row, 15540 ms to end, start offset by 3231 ms.
                                                        ->  Hash Left Join  (cost=0.000..13684.640 rows=1 width=218)
                                                                Hash Cond: sierra_bravo.alpha_kilo.november_six = sierra_bravo.alpha_kilo.november_six
                                                                Rows out:  Avg 154.7 rows x 36 workers.  Max 1885 rows (seg40) with 14745 ms to first row, 15501 ms to end, start offset by 3268 ms.
                                                                Executor memory:  36K bytes avg, 39K bytes max (seg47).
                                                                Work_mem used:  36K bytes avg, 39K bytes max (seg47). Workfile: (0 spilling, 0 reused)
                                                                (seg40)  Hash chain length 1.0 avg, 1 max, using 1200 of 65539 buckets.
                                                                (seg47)  Hash chain length 1.0 avg, 1 max, using 1222 of 65539 buckets.
                                                              ->  Redistribute Motion 56:56  (slice3; segments: 56)  (cost=0.000..13252.890 rows=1 width=215)
                                                                      Hash Key: gpgen_cr.cmc_grgr_group.grgr_ck
                                                                      Rows out:  Avg 154.7 rows x 36 workers at destination.  Max 1885 rows (seg40) with 14744 ms to first row, 15497 ms to end, start offset by 3270 ms.
                                                                    ->  Hash Left Join  (cost=0.000..13252.890 rows=1 width=215)
                                                                            Hash Cond: tango.uniform_victor::bpchar = delta.uniform_charlie
                                                                            Join Filter: "oscar_romeo".echo_romeo = ANY ('bravo'::bpchar[])
                                                                            Rows out:  Avg 99.5 rows x 56 workers.  Max 122 rows (seg21) with 10987 ms to first row, 15055 ms to end, start offset by 3229 ms.
                                                                            Executor memory:  11841K bytes avg, 11880K bytes max (seg24).
                                                                            Work_mem used:  11841K bytes avg, 11880K bytes max (seg24). Workfile: (56 spilling, 0 reused)
                                                                            Work_mem wanted: 348015K bytes avg, 376875K bytes max (seg14) to lessen workfile I/O affecting 56 workers.
                                                                            (seg14)  Initial batch 0:
                                                                            (seg14)    Wrote 315136K bytes to inner workfile.
                                                                            (seg14)  Initial batches 1..31:
                                                                            (seg14)    Read 315325K bytes from inner workfile: 10172K avg x 31 nonempty batches, 10233K max.
                                                                            (seg14)    Read 19K bytes from outer workfile: 1K avg x 31 nonempty batches, 2K max.
                                                                            (seg14)  Hash chain length 2.4 avg, 12 max, using 1837494 of 2097248 buckets.
                                                                            (seg21)  Initial batch 0:
                                                                            (seg21)    Wrote 315184K bytes to inner workfile.
                                                                            (seg21)  Initial batches 1..31:
                                                                            (seg21)    Read 305120K bytes from inner workfile: 10171K avg x 30 nonempty batches, 10214K max.
                                                                            (seg21)    Read 25K bytes from outer workfile: 1K avg x 30 nonempty batches, 3K max.
                                                                            (seg21)  Hash chain length 2.4 avg, 12 max, using 1779580 of 2031709 buckets.  Skipped 1 empty batches.
                                                                          ->  Result  (cost=0.000..3181.100 rows=724580 width=173)
                                                                                  Rows out:  Avg 99.5 rows x 56 workers.  Max 122 rows (seg21) with 2873 ms to first row, 5923 ms to end, start offset by 11252 ms.
                                                                                ->  Redistribute Motion 56:56  (slice2; segments: 56)  (cost=0.000..3055.750 rows=724580 width=112)
                                                                                        Hash Key: echo_tango
                                                                                        Rows out:  Avg 99.5 rows x 56 workers at destination.  Max 122 rows (seg21) with 2873 ms to first row, 5921 ms to end, start offset by 11252 ms.
                                                                                      ->  Hash Join  (cost=0.000..2801.740 rows=724580 width=112)
                                                                                              Hash Cond: tango.four_golf::bpchar = sierra_bravo.alpha_kilo.yankee_romeo
                                                                                              Rows out:  Avg 99.5 rows x 56 workers.  Max 108 rows (seg26) with 49 ms to first row, 11626 ms to end, start offset by 3252 ms.
                                                                                              Executor memory:  2495K bytes avg, 2495K bytes max (seg0).
                                                                                              Work_mem used:  2495K bytes avg, 2495K bytes max (seg0). Workfile: (0 spilling, 0 reused)
                                                                                              (seg26)  Hash chain length 1.6 avg, 9 max, using 40839 of 65539 buckets.
                                                                                            ->  Table Scan on tbl_3  (cost=0.000..1881.590 rows=724580 width=117)
                                                                                                    Filter: CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform_kilo'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo_victor'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo_victor'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra_lima'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra_lima'::interval ELSE papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END >= 'whiskey'::timestamp without time zone AND CASE WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 6::double precision AND (papa_five('six'::text, echo_november) + 'four_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'four_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND papa_five('six'::text, echo_november) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) AND echo_november < papa_delta(xray(echo_november, 'papa_uniform'::text) || 'uniform_kilo'::text, 'seven_victor'::text) THEN papa_five('six'::text, echo_november) WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 5::double precision AND (papa_five('six'::text, echo_november) + 'romeo_victor'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'romeo_victor'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 4::double precision AND (papa_five('six'::text, echo_november) + 'seven_oscar'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'seven_oscar'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 3::double precision AND (papa_five('six'::text, echo_november) + 'hotel'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'hotel'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 2::double precision AND (papa_five('six'::text, echo_november) + 'alpha_delta'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'alpha_delta'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 1::double precision AND (papa_five('six'::text, echo_november) + 'november_yankee'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'november_yankee'::interval WHEN echo_tango('papa_sierra'::text, papa_five('six'::text, echo_november)) = 0::double precision AND (papa_five('six'::text, echo_november) + 'sierra_lima'::interval) <= (papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval) THEN papa_five('six'::text, echo_november) + 'sierra_lima'::interval ELSE papa_five('two_charlie'::text, papa_five('six'::text, echo_november)) + 'mike_charlie'::interval END < 'mike_yankee'::timestamp without time zone
                                                                                                    Rows out:  Avg 99.6 rows x 56 workers.  Max 108 rows (seg26) with 5.899 ms to first row, 11580 ms to end, start offset by 3295 ms.
                                                                                            ->  Hash  (cost=433.330..433.330 rows=63856 width=13)
                                                                                                    Rows in:  Avg 63853.0 rows x 56 workers.  Max 63853 rows (seg0) with 70 ms to end, start offset by 3224 ms.
                                                                                                  ->  Broadcast Motion 56:56  (slice1; segments: 56)  (cost=0.000..433.330 rows=63856 width=13)
                                                                                                          Rows out:  Avg 63853.0 rows x 56 workers at destination.  Max 63853 rows (seg0) with 0.071 ms to first row, 50 ms to end, start offset by 3224 ms.
                                                                                                        ->  Table Scan on tbl_2  (cost=0.000..431.440 rows=1141 width=13)
                                                                                                                Rows out:  Avg 1140.2 rows x 56 workers.  Max 1222 rows (seg47) with 0.058 ms to first row, 0.661 ms to end, start offset by 3228 ms.
                                                                          ->  Hash  (cost=1392.980..1392.980 rows=4383595 width=55)
                                                                                  Rows in:  Avg 137051.9 rows x 56 workers.  Max 138139 rows (seg49) with 7375 ms to end, start offset by 3251 ms.
                                                                                ->  Table Scan on tbl_1  (cost=0.000..1392.980 rows=4383595 width=55)
                                                                                        Rows out:  Avg 4383421.4 rows x 56 workers.  Max 4386998 rows (seg18) with 13 ms to first row, 4067 ms to end, start offset by 3268 ms.
                                                              ->  Hash  (cost=431.440..431.440 rows=1141 width=7)
                                                                      Rows in:  Avg 1140.2 rows x 56 workers.  Max 1222 rows (seg47) with 1.085 ms to end, start offset by 3227 ms.
                                                                    ->  Table Scan on tbl_2  (cost=0.000..431.440 rows=1141 width=7)
                                                                            Rows out:  Avg 1140.2 rows x 56 workers.  Max 1222 rows (seg47) with 0.077 ms to first row, 0.675 ms to end, start offset by 3227 ms. 

Resolution

Follow the steps mentioned below to resolve this issue:

  • Use the Pivotal Optimizer
  • If you are unable to use the Pivotal Optimizer this functionality can be turned off by the legacy optimizer by setting the GUC gp_inline_simple_cte to FALSE.

Additional Information

http://gpdb.docs.pivotal.io/43150/ref_guide/config_params/guc-list.html#gp_inline_simple_cte

Comments

Powered by Zendesk