|Pivotal HDB||1.3.x / 2.x|
When doing a hash join against very large datasets, it errors out, giving the following message,
- Extreme skew in the innerside of Hashjoin
- Could not write to temporary file: No space left on device
Error Messages from pg_log -
"Extreme skew in the innerside of Hashjoin, nbatch 32, mintuples 6672, maxtuples 1258717",,,,,,"
create table summary_topic_tmp
with (appendonly=true, orientation=parquet, compresstype=snappy) as
select t2.reader_id, t3.topic_id, sum(t2.pv) as pv from
select st.reader_id, st.vertical_id, sum(st.pv) as pv from
select date, reader_id, vertical_id, pv from daily_vertical_201609
select date, reader_id, vertical_id, pv from daily_vertical_201608
select date, reader_id, vertical_id, pv from daily_vertical_201607
select date, reader_id, vertical_id, pv from daily_vertical_201606
where st.date between '2016-06-26' and '2016-09-24'
group by st.reader_id, st.vertical_id
inner join relay_vertical_googletopic t3
on t2.vertical_id = t3.vertical_id
group by t2.reader_id, t3.topic_id;
"ERROR","58030","could not write to temporary file: No space left on device",,,,,,"
The output from the query shows that the stats for the related tables were not updated and it caused extreme skew and subsequent large spills on a certain segment node which caused the 'No space left on device' error.
Explain output feed into PlanChecker shows 'rows=1' which suggests the table stats are not up to date -
-> Table Scan on daily_vertical_201609 (cost=0.00..431.00 rows=1 width=28)
Filter: date >= '2016-06-26'::date AND date <= '2016-09-24'::date
WARNING: Estimated rows is 1 | May need to run ANALYZE on table "daily_vertical_201609"
To resolve this issue, as suggested by the PlanChecker, run 'analyze' against all the tables associated with the failed query like shown below -
gpadmin-# analyze daily_vertical_201606
gpadmin-# analyze daily_vertical_201607
gpadmin-# analyze daily_vertical_201608
gpadmin-# analyze daily_vertical_201609