From: Tom Lane <tgl@sss.pgh.pa.us>
To: Krzysztof Nienartowicz <Krzysztof.Nienartowicz@unige.ch>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Query causing explosion of temp space with join involving partitioning
Date: Thu, 27 May 2010 12:41:28 -0400
Message-ID: <3578.1274978488@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTiljwIjxXQU-TFrt1qYky3dkxDLUAAWUss35dnFy@mail.gmail.com>
References: <AANLkTiljwIjxXQU-TFrt1qYky3dkxDLUAAWUss35dnFy@mail.gmail.com>
Krzysztof Nienartowicz <Krzysztof.Nienartowicz@unige.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Krzysztof.Nienartowicz@unige.ch, pgsql-bugs@postgresql.org
Subject: Re: Query causing explosion of temp space with join involving partitioning
In-Reply-To: <3578.1274978488@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox