Michael, below is the query and the execution plan. Yes the tables are partitioned and its using parallel options. Yes I could do the upgrade if I can show the benefits, please check the explain plan and let me know what pg12 features you are referring to which could help.
SELECT
...
250+ cols from various below tables
...
FROM
x.table1 yankee_charlie
INNER JOIN x.juliet_juliet juliet_alpha ON
yankee_charlie.bravo_tango = juliet_alpha.kilo_four
LEFT OUTER JOIN y.sierra_delta kilo_foxtrot ON
yankee_charlie.sierra_six = kilo_foxtrot.november_juliet
LEFT OUTER JOIN z.xray_bravo uniform_delta ON
yankee_charlie.alpha_four = uniform_delta.papa_mike
LEFT OUTER JOIN x.papa_whiskey india_five ON
yankee_charlie.golf = india_five.tango_mike
LEFT OUTER JOIN x.lima_romeo hotel ON
yankee_charlie.zulu_oscar = hotel.bravo_hotel
LEFT OUTER JOIN x.romeo_golf foxtrot_whiskey_two ON
yankee_charlie.xray_alpha = foxtrot_whiskey_two.tango_quebec
LEFT OUTER JOIN a.seven_yankee zulu_four ON
yankee_charlie.uniform_india = zulu_four.seven_bravo
LEFT OUTER JOIN a.quebec_november kilo_lima ON
zulu_four.mike_four = kilo_lima.lima_uniform
LEFT OUTER JOIN a.tango_romeo romeo_xray_echo ON
kilo_lima.lima_uniform = romeo_xray_echo.lima_uniform
LEFT OUTER JOIN b.seven_three four_hotel ON
kilo_lima.zulu_three = four_hotel.whiskey_victor_bravo
LEFT OUTER JOIN x.juliet_yankee five_quebec ON
yankee_charlie.oscar_india = five_quebec.six_xray
LEFT OUTER JOIN z.romeo_two delta_mike ON
yankee_charlie.four_zulu = delta_mike.whiskey_victor_seven
LEFT OUTER JOIN z.delta_lima six_alpha ON
yankee_charlie.xray_three = six_alpha.kilo_whiskey
LEFT OUTER JOIN x.five_hotel xray_quebec ON
yankee_charlie.bravo_tango = xray_quebec.kilo_four
LEFT OUTER JOIN y.sierra_delta mike_foxtrot ON
yankee_charlie.two = mike_foxtrot.november_juliet
LEFT OUTER JOIN y.sierra_delta india_three ON
yankee_charlie.victor = india_three.november_juliet
WHERE
yankee_charlie.romeo_xray_two >= (CURRENT_DATE - INTERVAL '5 years')
AND yankee_charlie.romeo_xray_two < papa_five('year',(CURRENT_DATE + INTERVAL '1 year')) - INTERVAL '1 day';
Gather (cost=33464846.41..475412138.09 rows=97965031 width=7161)
Workers Planned: 2
-> Parallel Hash Left Join (cost=33463846.41..465614634.99 rows=40818763 width=7161)
Hash Cond: (yankee_charlie.victor = india_three.november_juliet)
-> Parallel Hash Left Join (cost=33330811.86..392519286.24 rows=40818763 width=7109)
Hash Cond: (yankee_charlie.two = mike_foxtrot.november_juliet)
-> Hash Left Join (cost=33197777.31..321716804.91 rows=40818763 width=7056)
Hash Cond: (yankee_charlie.xray_three = six_alpha.kilo_whiskey)
-> Hash Left Join (cost=33197713.71..321608781.15 rows=40818763 width=7003)
Hash Cond: (yankee_charlie.four_zulu = delta_mike.whiskey_victor_seven)
-> Hash Left Join (cost=33197035.05..321500899.07 rows=40818763 width=6863)
Hash Cond: (yankee_charlie.oscar_india = five_quebec.six_xray)
-> Parallel Hash Left Join (cost=33196883.64..321393345.56 rows=40818763 width=6813)
Hash Cond: (yankee_charlie.bravo_tango = xray_quebec.kilo_four)
-> Parallel Hash Left Join (cost=29850433.79..255866124.43 rows=40818763 width=6125)
Hash Cond: (zulu_four.mike_four = kilo_lima.lima_uniform)
-> Hash Left Join (cost=27572665.00..192250116.73 rows=40818763 width=6070)
Hash Cond: (yankee_charlie.zulu_oscar = hotel.bravo_hotel)
-> Parallel Hash Left Join (cost=27571519.35..192141780.40 rows=40818763 width=6042)
Hash Cond: (yankee_charlie.alpha_four = uniform_delta.papa_mike)
-> Parallel Hash Join (cost=27569303.10..192032398.49 rows=40818763 width=5775)
Hash Cond: (yankee_charlie.bravo_tango = foxtrot_whiskey_bravo2.kilo_four)
-> Parallel Hash Left Join (cost=3696445.91..128666530.60 rows=40818763 width=2497)
Hash Cond: (yankee_charlie.sierra_six = kilo_foxtrot.november_juliet)
-> Parallel Hash Left Join (cost=3550202.36..106708533.27 rows=40818763 width=2147)
Hash Cond: (yankee_charlie.xray_alpha = foxtrot_whiskey_two.tango_quebec)
-> Parallel Hash Left Join (cost=1366012.90..84660500.52 rows=40818763 width=1926)
Hash Cond: (yankee_charlie.uniform_india = zulu_four.seven_bravo)
-> Parallel Hash Left Join (cost=3031.30..65010702.64 rows=40818763 width=1781)
Hash Cond: (yankee_charlie.golf = india_five.tango_mike)
-> Parallel Append (cost=0.12..64900513.56 rows=40818780 width=1835)
Subplans Removed: 25
-> Parallel Index Scan using november_mike on quebec_victor yankee_charlie (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using alpha_six on xray_foxtrot romeo_oscar (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using whiskey_three on yankee_bravo charlie (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using quebec_india on five_yankee quebec_foxtrot (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Seq Scan on romeo_zulu oscar_seven (cost=0.00..48358699.80 rows=30351009 width=1975)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Seq Scan on xray_seven five_golf (cost=0.00..16337483.57 rows=10467742 width=1428)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE + 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Hash (cost=2643.30..2643.30 rows=31030 width=76)
-> Parallel Seq Scan on papa_whiskey india_five (cost=0.00..2643.30 rows=31030 width=76)
-> Parallel Hash (cost=1131848.93..1131848.93 rows=5529893 width=210)
-> Parallel Seq Scan on seven_yankee zulu_four (cost=0.00..1131848.93 rows=5529893 width=210)
-> Parallel Hash (cost=1797395.54..1797395.54 rows=6623354 width=351)
-> Parallel Seq Scan on romeo_golf foxtrot_whiskey_two (cost=0.00..1797395.54 rows=6623354 width=351)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=415)
-> Parallel Seq Scan on sierra_delta kilo_foxtrot (cost=0.00..122037.80 rows=365580 width=415)
-> Parallel Hash (cost=10995540.28..10995540.28 rows=30397353 width=3343)
-> Parallel Append (cost=0.00..10995540.28 rows=30397353 width=3343)
-> Parallel Seq Scan on kilo_november lima_five (cost=0.00..8362872.25 rows=22396025 width=3681)
-> Parallel Seq Scan on five_hotel foxtrot_oscar (cost=0.00..2480681.27 rows=8001327 width=2398)
-> Parallel Seq Scan on juliet_juliet juliet_alpha (cost=0.00..0.00 rows=1 width=7566)
-> Parallel Hash (cost=2012.78..2012.78 rows=16278 width=332)
-> Parallel Seq Scan on xray_bravo uniform_delta (cost=0.00..2012.78 rows=16278 width=332)
-> Hash (cost=1008.62..1008.62 rows=10962 width=158)
-> Seq Scan on lima_romeo hotel (cost=0.00..1008.62 rows=10962 width=158)
-> Parallel Hash (cost=2112348.26..2112348.26 rows=4256042 width=185)
-> Hash Left Join (cost=671798.94..2112348.26 rows=4256042 width=185)
Hash Cond: (kilo_lima.zulu_three = four_hotel.whiskey_victor_bravo)
-> Parallel Hash Left Join (cost=670990.36..2100358.89 rows=4256042 width=188)
Hash Cond: (kilo_lima.lima_uniform = romeo_xray_echo.lima_uniform)
-> Parallel Seq Scan on quebec_november kilo_lima (cost=0.00..1156375.42 rows=4256042 width=176)
-> Parallel Hash (cost=563811.27..563811.27 rows=4253927 width=77)
-> Parallel Seq Scan on tango_romeo romeo_xray_echo (cost=0.00..563811.27 rows=4253927 width=77)
-> Hash (cost=740.48..740.48 rows=5448 width=127)
-> Seq Scan on seven_three four_hotel (cost=0.00..740.48 rows=5448 width=127)
-> Parallel Hash (cost=2480681.27..2480681.27 rows=8001327 width=753)
-> Parallel Seq Scan on five_hotel xray_quebec (cost=0.00..2480681.27 rows=8001327 width=753)
-> Hash (cost=128.96..128.96 rows=1796 width=180)
-> Seq Scan on juliet_yankee five_quebec (cost=0.00..128.96 rows=1796 width=180)
-> Hash (cost=573.85..573.85 rows=8385 width=270)
-> Seq Scan on romeo_two delta_mike (cost=0.00..573.85 rows=8385 width=270)
-> Hash (cost=56.60..56.60 rows=560 width=183)
-> Seq Scan on delta_lima six_alpha (cost=0.00..56.60 rows=560 width=183)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=118)
-> Parallel Seq Scan on sierra_delta mike_foxtrot (cost=0.00..122037.80 rows=365580 width=118)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=118)
-> Parallel Seq Scan on sierra_delta india_three (cost=0.00..122037.80 rows=365580 width=118)On Thu, Aug 13, 2020 at 1:10 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:
On Wed, Aug 12, 2020 at 5:52 PM Ayub M <hiayub@xxxxxxxxx> wrote:This is PostgreSQL 11 on AWS, there is a mview query in this OLAP database, the tables involved are huge - 50-100m records on average records hundreds of columns in most cases.
How many tables and how many partitions each? Can you share an EXPLAIN output? Are the tables being joined partitioned in such a way to allow partition wise joins? Have you enabled partition wise joins config? There are many enhancements for partitioning in PG12, do you have the option to upgrade?
Regards,
Ayub
