Skip to content

Instantly share code, notes, and snippets.

@kzzzr

kzzzr/CTE_query_plan

Created January 27, 2022 20:00
Show Gist options
  • Save kzzzr/6499510ac7fa0004fd32ed30e1df4541 to your computer and use it in GitHub Desktop.
Save kzzzr/6499510ac7fa0004fd32ed30e1df4541 to your computer and use it in GitHub Desktop.
Download ZIP
Excessive CTE usage in Data Mart calculation
Raw
CTE_query_plan
XN Merge Left Join DS_DIST_NONE (cost=6768461698952.58..64203387414945.72 rows=11491303 width=4361)
-> XN Hash Left Join DS_BCAST_INNER (cost=5768461175265.66..63203382323457.28 rows=11491303 width=3845)
-> XN Merge Left Join DS_DIST_NONE (cost=4765778722374.23..62200689246924.30 rows=11491303 width=3321)
-> XN Hash Left Join DS_DIST_INNER (cost=4765778722374.23..62200689217006.26 rows=11491303 width=3285)
-> XN Merge Left Join DS_DIST_NONE (cost=3765767462785.01..36310404425364.76 rows=11491303 width=3284)
-> XN Hash Left Join DS_DIST_NONE (cost=3765767462785.01..36310403604540.56 rows=11491303 width=3276)
-> XN Hash Left Join DS_DIST_INNER (cost=3765766240852.13..36310400055617.20 rows=11491303 width=3251)
-> XN Hash Left Join DS_DIST_NONE (cost=1765764690131.17..34310372401253.97 rows=11491303 width=3250)
-> XN Hash Left Join DS_DIST_NONE (cost=1765764584059.78..34308739121313.73 rows=11491303 width=2734)
-> XN Hash Left Join DS_DIST_INNER (cost=1765764459940.76..34295149573726.90 rows=11491303 width=2218)
-> XN Merge Left Join DS_DIST_NONE (cost=1000010031297.10..32223714202787.90 rows=11491303 width=2114)
-> XN Hash Left Join DS_DIST_INNER (cost=1000010031297.10..32223714087329.01 rows=11491303 width=2101)
-> XN Hash Left Join DS_DIST_NONE (cost=9577381.38..31223228444937.88 rows=11491303 width=2061)
-> XN Hash Left Join DS_DIST_INNER (cost=8712091.80..31067435823702.83 rows=11491303 width=2005)
-> XN Hash Left Join DS_DIST_INNER (cost=7859418.88..30619916801396.41 rows=11491303 width=1989)
-> XN Hash Left Join DS_DIST_INNER (cost=7008121.59..30229124878694.88 rows=11491303 width=1973)
-> XN Hash Left Join DS_DIST_INNER (cost=6003365.01..23609197751093.84 rows=11491303 width=1965)
-> XN Hash Left Join DS_DIST_INNER (cost=4996010.00..16883698604277.77 rows=11491303 width=1957)
-> XN Hash Left Join DS_DIST_INNER (cost=3967179.87..9285685053765.01 rows=11491303 width=1949)
-> XN Hash Left Join DS_DIST_NONE (cost=2932923.63..1467214599917.02 rows=11491303 width=1941)
-> XN Hash Left Join DS_DIST_NONE (cost=2208697.76..1467212124905.13 rows=11491303 width=1925)
-> XN Hash Left Join DS_DIST_NONE (cost=1484561.77..1467209650276.11 rows=11491303 width=1909)
-> XN Hash Left Join DS_DIST_NONE (cost=761217.95..1467207179021.71 rows=11491303 width=1893)
-> XN Merge Left Join DS_DIST_NONE (cost=37115.40..1467204704535.15 rows=11491303 width=1877)
-> XN Hash Left Join DS_BCAST_INNER (cost=37115.40..1467204391095.36 rows=11491303 width=1652)
-> XN Hash Left Join DS_BCAST_INNER (cost=37113.61..1467095456421.45 rows=11491303 width=1612)
-> XN Hash Join DS_DIST_ALL_NONE (cost=22558.30..721862974128.73 rows=11491303 width=1587)
-> XN Hash Left Join DS_BCAST_INNER (cost=22558.18..721862686846.04 rows=13789563 width=1553)
-> XN Merge Left Join DS_DIST_NONE (cost=0.00..479598.34 rows=13789563 width=1552)
-> XN Seq Scan on mv_tbl__stg_orders__0 derived_table2 (cost=0.00..137895.63 rows=13789563 width=1245)
-> XN Seq Scan on orders_price_details_processing (cost=0.00..137895.63 rows=13789563 width=335)
-> XN Hash (cost=18046.54..18046.54 rows=1804654 width=29)
-> XN Seq Scan on dim_users (cost=0.00..18046.54 rows=1804654 width=29)
-> XN Hash (cost=0.10..0.10 rows=10 width=62)
-> XN Seq Scan on mv_tbl__stg_cities__0 (cost=0.00..0.10 rows=10 width=62)
-> XN Hash (cost=11644.25..11644.25 rows=1164425 width=53)
-> XN Seq Scan on mv_tbl__stg_cards__0 (cost=0.00..11644.25 rows=1164425 width=53)
-> XN Hash (cost=1.43..1.43 rows=143 width=68)
-> XN Seq Scan on stg_vendors (cost=0.00..1.43 rows=143 width=68)
-> XN Seq Scan on requests_hexagons_out hexagons (cost=0.00..137875.90 rows=13787590 width=253)
-> XN Hash (cost=689478.15..689478.15 rows=13849761 width=44)
-> XN Seq Scan on passengers_requests_zones (cost=0.00..689478.15 rows=13849761 width=44)
-> XN Hash (cost=689478.15..689478.15 rows=13546267 width=44)
-> XN Seq Scan on passengers_requests_zones (cost=0.00..689478.15 rows=13546267 width=44)
-> XN Hash (cost=689478.15..689478.15 rows=13863137 width=44)
-> XN Seq Scan on passengers_requests_zones (cost=0.00..689478.15 rows=13863137 width=44)
-> XN Hash (cost=689478.15..689478.15 rows=13899088 width=44)
-> XN Seq Scan on passengers_requests_zones (cost=0.00..689478.15 rows=13899088 width=44)
-> XN Hash (cost=1026939.66..1026939.66 rows=2926631 width=524)
-> XN Subquery Scan events_looking_for_drivers (cost=983040.19..1026939.66 rows=2926631 width=524)
-> XN HashAggregate (cost=983040.19..997673.35 rows=2926631 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=14121949 width=85)
-> XN Hash (cost=1021719.87..1021719.87 rows=2844109 width=524)
-> XN Subquery Scan events_on_the_way (cost=979058.23..1021719.87 rows=2844109 width=524)
-> XN HashAggregate (cost=979058.23..993278.78 rows=2844109 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=13723753 width=85)
-> XN Hash (cost=1001061.23..1001061.23 rows=2517507 width=524)
-> XN Subquery Scan events_arrived (cost=963298.63..1001061.23 rows=2517507 width=524)
-> XN HashAggregate (cost=963298.63..975886.17 rows=2517507 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=12147793 width=85)
-> XN Hash (cost=998561.62..998561.62 rows=2477989 width=524)
-> XN Subquery Scan events_serving (cost=961391.78..998561.62 rows=2477989 width=524)
-> XN HashAggregate (cost=961391.78..973781.73 rows=2477989 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=11957108 width=85)
-> XN Hash (cost=850936.98..850936.98 rows=144124 width=532)
-> XN Subquery Scan events_car_not_found (cost=848775.12..850936.98 rows=144124 width=532)
-> XN HashAggregate (cost=848775.12..849495.74 rows=144124 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=695442 width=85)
-> XN Hash (cost=852260.30..852260.30 rows=165045 width=532)
-> XN Subquery Scan events_extrastop (cost=849784.63..852260.30 rows=165045 width=532)
-> XN HashAggregate (cost=849784.63..850609.85 rows=165045 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=796393 width=85)
-> XN Hash (cost=831396.23..831396.23 rows=13557341 width=572)
-> XN Subquery Scan logs (cost=458569.35..831396.23 rows=13557341 width=572)
-> XN HashAggregate (cost=458569.35..695822.82 rows=13557341 width=96)
-> XN Seq Scan on mv_tbl__flt_orders_logs__0 (cost=0.00..152856.45 rows=15285645 width=96)
-> XN Hash (cost=1000000453912.77..1000000453912.77 rows=1179 width=72)
-> XN Subquery Scan reserved_rows (cost=1000000446249.73..1000000453912.77 rows=1179 width=72)
-> XN Merge (cost=1000000446249.73..1000000450965.45 rows=235786 width=136)
-> XN Network (cost=1000000446249.73..1000000450965.45 rows=235786 width=136)
-> XN Window (cost=1000000446249.73..1000000450965.45 rows=235786 width=136)
-> XN Sort (cost=1000000446249.73..1000000446839.19 rows=235786 width=136)
-> XN Network (cost=425209.23..425209.23 rows=235786 width=136)
-> XN HashAggregate (cost=425209.23..425209.23 rows=235786 width=136)
-> XN Subquery Scan joined_logs (cost=0.00..395736.00 rows=2357858 width=136)
-> XN Unique (cost=0.00..372157.42 rows=2357858 width=139)
-> XN Append (cost=0.00..330894.91 rows=2357858 width=139)
-> XN Subquery Scan "*SELECT* 1" (cost=0.00..139824.33 rows=2357857 width=137)
-> XN Seq Scan on mv_tbl__stg_audit__0 derived_table2 (cost=0.00..116245.76 rows=2357857 width=137)
-> XN Subquery Scan "*SELECT* 2" (cost=0.00..191070.57 rows=1 width=139)
-> XN Seq Scan on mv_tbl__flt_orders_logs__0 (cost=0.00..191070.56 rows=1 width=139)
-> XN Seq Scan on mv_tbl__stg_fraud_scores__0 (cost=0.00..48271.08 rows=3786966 width=41)
-> XN Hash (cost=765754422814.73..765754422814.73 rows=2331572 width=104)
-> XN Hash Right Join DS_DIST_INNER (cost=29144.65..765754422814.73 rows=2331572 width=104)
-> XN Seq Scan on mv_tbl__stg_promo_codes__0 (cost=0.00..28335.75 rows=2833575 width=76)
-> XN Hash (cost=23315.72..23315.72 rows=2331572 width=56)
-> XN Seq Scan on f_passengers_discounts d (cost=0.00..23315.72 rows=2331572 width=56)
-> XN Hash (cost=121162.58..121162.58 rows=1182576 width=516)
-> XN Subquery Scan with_calls_chauffeur_to_passenger (cost=0.00..121162.58 rows=1182576 width=516)
-> XN Unique (cost=0.00..109336.82 rows=1182576 width=28)
-> XN Seq Scan on mv_tbl__stg_calls__0 (cost=0.00..103606.23 rows=2292237 width=28)
-> XN Hash (cost=105716.09..105716.09 rows=142118 width=516)
-> XN Subquery Scan with_calls_passenger_to_chauffeur (cost=0.00..105716.09 rows=142118 width=516)
-> XN Unique (cost=0.00..104294.91 rows=142118 width=28)
-> XN Seq Scan on mv_tbl__stg_calls__0 (cost=0.00..103606.23 rows=275473 width=28)
-> XN Hash (cost=2000001550720.67..2000001550720.67 rows=118 width=33)
-> XN Subquery Scan prebook_to_asap (cost=2000001550719.20..2000001550720.67 rows=118 width=33)
-> XN HashAggregate (cost=2000001550719.20..2000001550719.49 rows=118 width=96)
-> XN Hash Join DS_DIST_NONE (cost=2000001504988.05..2000001550520.53 rows=39733 width=96)
-> XN Subquery Scan a (cost=1000001051072.33..1000001088138.10 rows=6740 width=64)
-> XN Window (cost=1000001051072.33..1000001071290.02 rows=1347846 width=72)
-> XN Sort (cost=1000001051072.33..1000001054441.95 rows=1347846 width=72)
-> XN Network (cost=913846.62..913846.62 rows=1347846 width=72)
-> XN HashAggregate (cost=913846.62..913846.62 rows=1347846 width=72)
-> XN Subquery Scan joined_logs (cost=0.00..812758.24 rows=13478451 width=72)
-> XN Unique (cost=0.00..677973.73 rows=13478451 width=139)
-> XN Append (cost=0.00..442100.84 rows=13478451 width=139)
-> XN Subquery Scan "*SELECT* 1" (cost=0.00..116761.08 rows=51532 width=137)
-> XN Seq Scan on mv_tbl__stg_audit__0 derived_table2 (cost=0.00..116245.76 rows=51532 width=137)
-> XN Subquery Scan "*SELECT* 2" (cost=0.00..325339.75 rows=13426919 width=139)
-> XN Seq Scan on mv_tbl__flt_orders_logs__0 (cost=0.00..191070.56 rows=13426919 width=139)
-> XN Hash (cost=1000000453912.77..1000000453912.77 rows=1179 width=64)
-> XN Subquery Scan r (cost=1000000446249.73..1000000453912.77 rows=1179 width=64)
-> XN Window (cost=1000000446249.73..1000000450965.45 rows=235786 width=136)
-> XN Sort (cost=1000000446249.73..1000000446839.19 rows=235786 width=136)
-> XN Network (cost=425209.23..425209.23 rows=235786 width=136)
-> XN HashAggregate (cost=425209.23..425209.23 rows=235786 width=136)
-> XN Subquery Scan joined_logs (cost=0.00..395736.00 rows=2357858 width=136)
-> XN Unique (cost=0.00..372157.42 rows=2357858 width=139)
-> XN Append (cost=0.00..330894.91 rows=2357858 width=139)
-> XN Subquery Scan "*SELECT* 1" (cost=0.00..139824.33 rows=2357857 width=137)
-> XN Seq Scan on mv_tbl__stg_audit__0 derived_table2 (cost=0.00..116245.76 rows=2357857 width=137)
-> XN Subquery Scan "*SELECT* 2" (cost=0.00..191070.57 rows=1 width=139)
-> XN Seq Scan on mv_tbl__flt_orders_logs__0 (cost=0.00..191070.56 rows=1 width=139)
-> XN Hash (cost=1221932.38..1221932.38 rows=200 width=57)
-> XN Subquery Scan distributed (cost=1221925.88..1221932.38 rows=200 width=57)
-> XN HashAggregate (cost=1221925.88..1221930.38 rows=200 width=160)
-> XN Subquery Scan joined_logs (cost=0.00..979568.32 rows=19388604 width=160)
-> XN Unique (cost=0.00..785682.28 rows=19388604 width=139)
-> XN Append (cost=0.00..446381.71 rows=19388604 width=139)
-> XN Subquery Scan "*SELECT* 1" (cost=0.00..140924.07 rows=4128485 width=137)
-> XN Seq Scan on mv_tbl__stg_audit__0 derived_table2 (cost=0.00..99639.22 rows=4128485 width=137)
-> XN Subquery Scan "*SELECT* 2" (cost=0.00..305457.64 rows=15260119 width=139)
-> XN Seq Scan on mv_tbl__flt_orders_logs__0 (cost=0.00..152856.45 rows=15260119 width=139)
-> XN Seq Scan on f_passengers_requests_eta_checks (cost=0.00..673486.85 rows=11305507 width=36)
-> XN Hash (cost=1000011235852.47..1000011235852.47 rows=4747350 width=1033)
-> XN Subquery Scan on_the_way_on_time (cost=1000011152773.85..1000011235852.47 rows=4747350 width=1033)
-> XN HashAggregate (cost=1000011152773.85..1000011188378.97 rows=4747350 width=72)
-> XN Merge Join DS_DIST_NONE (cost=1000007454339.95..1000011107442.00 rows=6044246 width=72)
-> XN Subquery Scan e (cost=1000007454339.95..1000010551047.47 rows=53855783 width=532)
-> XN Window (cost=1000007454339.95..1000010012489.64 rows=53855783 width=122)
-> XN Sort (cost=1000007454339.95..1000007588979.40 rows=53855783 width=122)
-> XN Network (cost=0.00..538557.84 rows=53855783 width=122)
-> XN Seq Scan on wheely_prod_eta_measurements (cost=0.00..538557.84 rows=53855783 width=122)
-> XN Seq Scan on mv_tbl__stg_orders__0 derived_table2 (cost=0.00..137895.63 rows=4747350 width=64)
-> XN Seq Scan on stg_order_transfer_by_meters (cost=0.00..580.06 rows=58006 width=64)
-> XN Hash (cost=1002682452891.43..1002682452891.43 rows=1 width=1040)
-> XN Subquery Scan another_passenger_b2b_journeys (cost=1002682452891.36..1002682452891.43 rows=1 width=1040)
-> XN Window (cost=1002682452891.36..1002682452891.41 rows=2 width=72)
-> XN Sort (cost=1002682452891.36..1002682452891.37 rows=2 width=72)
-> XN Hash Join DS_BCAST_INNER (cost=2621932286.59..2682452891.36 rows=2 width=72)
-> XN Seq Scan on dim_users (cost=0.00..18046.54 rows=1804654 width=104)
-> XN Hash (cost=2621932286.45..2621932286.45 rows=54 width=104)
-> XN Hash Join DS_DIST_INNER (cost=1009881.97..2621932286.45 rows=54 width=104)
-> XN Seq Scan on mv_tbl__stg_orders__0 derived_table2 (cost=0.00..172369.54 rows=748164 width=132)
-> XN Hash (cost=1009879.47..1009879.47 rows=1000 width=516)
-> XN Subquery Scan e (cost=991284.55..1009879.47 rows=1000 width=516)
-> XN HashAggregate (cost=991284.55..1009869.47 rows=1000 width=85)
-> XN Seq Scan on mv_tbl__flt_orders_events__0 (cost=0.00..841820.70 rows=11957108 width=85)
-> XN Subquery Scan rc (cost=1000000089806.39..1000000092086.50 rows=415 width=1032)
-> XN Window (cost=1000000089806.39..1000000091050.09 rows=82913 width=63)
-> XN Sort (cost=1000000089806.39..1000000090013.68 rows=82913 width=63)
-> XN Network (cost=0.00..83032.69 rows=82913 width=63)
-> XN Seq Scan on mv_tbl__stg_audit__0 derived_table2 (cost=0.00..83032.69 rows=82913 width=63)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment