On Tue, Mar 9, 2021 at 8:13 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Radoslav Nedyalkov <rnedyal...@gmail.com> writes: > > On Tue, Mar 9, 2021 at 6:53 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > >> The only thing I'm aware of that could consume unbounded memory > >> on the server side is hash aggregation. (v13 has improved that > >> situation, but I'm guessing you are running some older version.) > >> The planner won't choose hash aggregation if it estimates that > >> the hash table would need to exceed work_mem, but its estimate > >> could be wrong. > > > Query and plan attached. > > Yeah, so, this looks suspicious: > > -> HashAggregate (cost=181320662.52..181323190.52 rows=252800 > width=16) > Group Key: gsm.merchant_id, cer.date > -> Merge Left Join (cost=161250580.17..170174828.82 > rows=891666696 width=71) > > How accurate is that estimate for the number of merchant_id/date > groups? Is the estimate for the size of the input join reasonable? > > Assuming this is the problem, a brute-force fix could be to turn off > enable_hashagg. But it'd be better to try to get the planner's > estimates more in line with reality. > > regards, tom lane >
Merely taking the number of rows from cross join inputs, N*M gives 1.4B rows + some multiplication from left join, that might by the "surprising" part, rows seem to be underestimated. Team already went to rewrite the query. Thanks for pointing out HashAggregate as a potential danger. Thanks and regards, Rado
