hash joins are causing no space left error

Started by Ayub Mover 5 years ago5 messagesgeneral

hiayub@gmail.com

over 5 years ago

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. The query runs for a while and then errors out
saying "No space left on device". I could see it generating around 500gb of
temp file data. At times it goes thru and at times it fails - probably due
to other queries running at the same time and causing failure.

The tables are partitioned and indexed on the PKs and FKs. Using
parallelism (4) with increased work_mem (4gb).

The joins are happening on around 10 tables and all are joining on the PK
and FK columns. I see partition pruning happening but the hash joins are
killing the query.

Is there any way to avoid hash joins? If we create hash indexes on the
joining columns, would PostgreSQL avoid hashing operation and instead use
hash indexes on the tables and join them. That way I feel resource
intensive hashing would be avoided and there wont be any need of temp
files. I tried but does not seem to work, when I query the table with
specific values then it uses the hash index but when I am joining the
tables it seems to do its own hash join.

My question is how to optimize massive table joins in PostgreSQL to resolve
- avoid space failures and make it run fast - takes a couple of hours to
complete now. Any best practices or suggestions.

Tom Lane

tgl@sss.pgh.pa.us

over 5 years ago

In reply to: Ayub M (#1)

Re: hash joins are causing no space left error

Ayub M <hiayub@gmail.com> writes:

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. The query runs for a while and then errors out
saying "No space left on device". I could see it generating around 500gb of
temp file data. At times it goes thru and at times it fails - probably due
to other queries running at the same time and causing failure.

Are you sure that these queries are actually producing the answers you
want? It sounds suspiciously like you are computing underconstrained
joins.

The joins are happening on around 10 tables and all are joining on the PK
and FK columns. I see partition pruning happening but the hash joins are
killing the query.
Is there any way to avoid hash joins?

TBH, you are asking the wrong question. A merge join would take about as
much temporary space, and a nestloop join over so much data would probably
not finish in an amount of time you're willing to wait. Indexes are NOT
a magic solution here. What you need to be thinking about is how to not
need to process so much data.

If you really need to have this proven to you, you can try "set
enable_hashjoin = off", but I don't think you'll find that better.

regards, tom lane

Michael Lewis

mlewis@entrata.com

over 5 years ago

In reply to: Ayub M (#1)

Re: hash joins are causing no space left error

On Wed, Aug 12, 2020 at 5:52 PM Ayub M <hiayub@gmail.com> 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?

Ayub M

hiayub@gmail.com

over 5 years ago

In reply to: Michael Lewis (#3)

Re: hash joins are causing no space left error

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_charlieINNER JOIN x.juliet_juliet juliet_alpha ON
yankee_charlie.bravo_tango = juliet_alpha.kilo_fourLEFT OUTER
JOIN y.sierra_delta kilo_foxtrot ON
yankee_charlie.sierra_six = kilo_foxtrot.november_julietLEFT
OUTER JOIN z.xray_bravo uniform_delta ON
yankee_charlie.alpha_four = uniform_delta.papa_mikeLEFT OUTER
JOIN x.papa_whiskey india_five ON
yankee_charlie.golf = india_five.tango_mikeLEFT OUTER JOIN
x.lima_romeo hotel ON
yankee_charlie.zulu_oscar = hotel.bravo_hotelLEFT OUTER JOIN
x.romeo_golf foxtrot_whiskey_two ON
yankee_charlie.xray_alpha =
foxtrot_whiskey_two.tango_quebecLEFT OUTER JOIN a.seven_yankee
zulu_four ON
yankee_charlie.uniform_india = zulu_four.seven_bravoLEFT OUTER
JOIN a.quebec_november kilo_lima ON
zulu_four.mike_four = kilo_lima.lima_uniformLEFT OUTER JOIN
a.tango_romeo romeo_xray_echo ON
kilo_lima.lima_uniform = romeo_xray_echo.lima_uniformLEFT
OUTER JOIN b.seven_three four_hotel ON
kilo_lima.zulu_three = four_hotel.whiskey_victor_bravoLEFT
OUTER JOIN x.juliet_yankee five_quebec ON
yankee_charlie.oscar_india = five_quebec.six_xrayLEFT OUTER
JOIN z.romeo_two delta_mike ON
yankee_charlie.four_zulu = delta_mike.whiskey_victor_sevenLEFT
OUTER JOIN z.delta_lima six_alpha ON
yankee_charlie.xray_three = six_alpha.kilo_whiskeyLEFT OUTER
JOIN x.five_hotel xray_quebec ON
yankee_charlie.bravo_tango = xray_quebec.kilo_fourLEFT OUTER
JOIN y.sierra_delta mike_foxtrot ON
yankee_charlie.two = mike_foxtrot.november_julietLEFT OUTER
JOIN y.sierra_delta india_three ON
yankee_charlie.victor = india_three.november_julietWHERE
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)