Re: BUG #18909: Query creates millions of temporary files and stalls

On Sat, 2025-05-03 at 19:56 +0200, Andrei Lepikhov wrote:
> [You don't often get email from lepihov(at)gmail(dot)com(dot) Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]
>
> On 3/5/2025 18:52, Sergey Koposov wrote:
> > On Sat, 2025-05-03 at 12:27 -0400, Tom Lane wrote:
> > > Yeah, this confirms the idea that the hashtable has exploded into an
> > > unreasonable number of buckets and batches. I don't know why a
> > > parallel hash join would be more prone to do that than a non-parallel
> > > one, though. I'm hoping some of the folks who worked on PHJ will
> > > look at this.
> > Here're my memory settings
> >
> > shared_buffers = 32GB
> > work_mem = 1GB
> May you show statistics from the pg_statistic table on two columns:
> "d.objid" and "gaps1.original_ext_source_id"? At least stanullfrac,
> stadistinct, stawidth and MCV frequencies.
>
> Also, an EXPLAIN ANALYZE on this query, which successfully finished
> execution without parallel workers, might provide quick insights.
>

For some reason, when running in psql with

wsdb=> set max_parallel_workers_per_gather =0;
SET
wsdb=> explain analyze select phot_g_mean_mag,phot_bp_mean_mag,phot_rp_mean_mag,
g.source_id,pmra,pmdec,parallax,parallax_error,d.ra,d.dec,d.ebv,d.gmeanpsfmag,rmeanpsfmag,imeanpsfmag,gkronmag,rkronmag from disk_sample1 as d left join
gaia_edr3_aux.panstarrs1bestneighbour as gaps1 on (gaps1.original_ext_source_id=d.objid) left join gaia_edr3.gaia_source as g on ( g.source_id = gaps1.source_id
) ;

it also triggered the issue, so it didn't finish.
(I don't quite know why I did the same thing earlier in python it worked fine, unless I made a mistake somehow
or the issue depends on which parts of the table are in the buffers)

And here are the statistics:

table_name | column_name | stanullfrac | stadistinct | stawidth | stakind1 |
stavalues1
| stakind2 | stavalues2 | stakind3 | stavalues3 | stakind4 | stavalues4 | stakind5 | stavalues5
---------------------------------------+------------------------+-------------+-------------+----------+----------+----------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+----------+------------+----------+------------+----------+------
------+----------+------------
gaia_edr3_aux.panstarrs1bestneighbour | original_ext_source_id | 0 | -1 | 8 | 2 |
{65821194190923049,69822775194516201,71021357366914725,71622558219298246,72242570679316167,72752715133276582,73292555331458103,73722640104185638,7431271120960725
3,74872844134480919,75461207930286818,75932867164620064,76482380938670301,77062576694809476,77652902237497049,78262718311176992,78892794541132028,795515044225764
94,80112867400946845,80772802603258902,81391421588335444,81971412486350795,82631186975787792,83252787960950269,83972820164120115,84752904153083118,85512619880528
302,86202647938176936,86842598063633314,87602612520706466,88421075961167122,89172651209793104,89952620931199224,90722572791560233,91650973331643328,9261269053096
6902,93602857051441783,94782658946712918,95712666148671194,96952828393314703,97932742026524718,98862853134788904,99802966167329820,100902836681868414,10190281236
5694460,103031292324379499,104431512205175644,105751238115030863,106953048414271619,108123145169985425,109550825872818887,111053588196313311,112282896513802828,1
13570942665251677,115022939740948640,116382768852523070,117662904386441014,118932892636732902,120190477050925736,121420948001414218,122442964708121838,1238132188
80751231,124992869043914348,126310843575958592,127562955440231806,128872831401617836,130233203538192704,131521007626061188,132613587058728897,134002801572489756,
135283031853672449,136872614522998101,138313019539925635,140112936758215218,141542930246066222,142983169221289589,144482954390781393,146082964742924866,147612878
246882308,149172975717758062,150602803315151189,152220866064652863,154243076414126838,156152890996068633,158242935213542742,160152946363967092,162193040985483908
,164030230662105236,166071022961061724,167933557037565204,169750502017714204,171783528800592714,173633125479747431,175841940675015146,178023541392200467,18008076
9816969530,182390090370071085,185103234223553859,189060291309395827,194410132668322056,213381935620486100} | 3 |
| 0 | | 0 | | 0 |
koposov.disk_sample1 | objid | 0 | -1 | 8 | 2 |
{80171356131152995,85481367757687896,88191325798310474,90321328433852673,92321466354661847,94201275367376529,95701503912853131,97161484423571822,9853144852720894
4,99921427833159308,101151276638551505,102331296875522814,103501223994628897,104551265261567639,105761212996488657,106981229573480759,108151464449806724,10952135
9876084906,110721535047532362,112031293425751247,112991512837345375,114041353644327525,115181393912959844,116271438699167868,117361157875578842,11846119946421047
5,119651383957806910,120691403987052979,121971437975375438,122991360553450551,124081345165940966,125561127251039774,126951413062522443,128261239226290575,1294911
91145884395,130701198797726914,131881390246784832,133071309574028386,134441245837162400,135561325470268624,136831168062713109,138031312799349329,1392611655099586
83,140541093855172591,141721073205266546,142881401626551876,144371304279438040,145581082546437325,147031098350718441,148451143937693348,149711230639891464,151101
129058019814,152461282899556445,153601374120407807,154871110028410102,156201081651853270,157471169532236664,158711380905398833,160070980700916736,161581282957436
146,163001203841617369,164491232859199607,165710958822329339,167161342475229839,168720942762011833,170101108127616821,171491208376278158,173061183798016157,17436
1022423370078,175860958433513392,177241069877220468,178661417914144713,180101182484225880,181550985775752317,183000983447406349,184530819926849727,18598116819371
2346,187450900510134237,188911315124166293,190320741585574970,191851126057417156,193291748947587292,194551520693837798,195430932247712984,196451520400227653,1976
70906570737306,198601716301982705,199510491344039825,200363325766562309,201183158865495520,201921012051408117,202533343632192769,203143424629645681,2039131989607
26463,204762033978144872,205573304352815754,206650175134906301,207582854953296137,208920557060736946,210870029621871940,215863110451068650} | 3 |
| 0 | | 0 | | 0 |
(2 rows)

Basically most of ids should be distinct but I would expect most objects form disk_sample1 have a 1 to 1 match in the gaps1 table

Sergey
The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.