Re: Query causing explosion of temp space with join involving partitioning

From: Krzysztof Nienartowicz <Krzysztof(dot)Nienartowicz(at)unige(dot)ch>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query causing explosion of temp space with join involving partitioning
Date: 2010-05-27 23:04:17
Message-ID: 639235E8-DA6E-4F50-B25D-B43FE8279369@unige.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-performance

I made a brute force check and indeed, for one of the parameters the query was switching to sequential scans (or bitmaps scans with condition on survey_pk=16 only if sequential scans were off). After closer look at the plan cardinalities I thought it would be worthy to increase histogram size and I set statistics on sources(srcid) to 1000 from default 10. It fixed the plan! Sources table was around 100M so skewness in this range must have been looking odd for the planner..
Thank you for the hints!
Best Regards,
Krzysztof
On May 27, 2010, at 6:41 PM, Tom Lane wrote:

> Krzysztof Nienartowicz <Krzysztof(dot)Nienartowicz(at)unige(dot)ch> writes:
>> Logs of the system running queries are not utterly clear, so chasing the
>> parameters for the explosive query is not that simple (shared logs between
>> multiple threads), but from what I see there is no difference between them
>> and the plan looks like (without removal of irrelevant parameters this time,
>> most of them are float8, but also bytea)
>> [ nestloop with inner index scans over the inherited table ]
>
> Well, that type of plan isn't going to consume much memory or disk
> space. What I suspect is happening is that sometimes, depending on the
> specific parameter values called out in the query, the planner is
> switching to another plan type that does consume lots of space (probably
> via sort or hash temp files). The most obvious guess is that that will
> happen when the range limits on srcid get far enough apart to make a
> nestloop not look cheap. You could try experimenting with EXPLAIN and
> different constant values to see what you get.
>
> regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2010-05-28 00:30:46 Re: PostgreSQL 9.0b1 - Error when checking table sizes
Previous Message Thom Brown 2010-05-27 22:03:21 Re: PostgreSQL 9.0b1 - Error when checking table sizes

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-05-28 01:16:26 Re: merge join killing performance
Previous Message Craig James 2010-05-27 21:44:19 Re: Does FILTER in SEQSCAN short-circuit AND?