From: Tom Lane <tgl@sss.pgh.pa.us>
To: krzysztof.nienartowicz@cern.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, 20 May 2010 15:21:49 -0400
Message-ID: <7409.1274383309@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTine_e2T4lpFfPMvneOtDyhA4u2eQCvtnMdcQb-y@mail.gmail.com>
References: <AANLkTine_e2T4lpFfPMvneOtDyhA4u2eQCvtnMdcQb-y@mail.gmail.com>
Krzysztof Nienartowicz <krzysztof.nienartowicz.cern@gmail.com> writes:
> surveys-> SELECT t1.SURVEY_PK, t1.SOURCE_PK, t1.TSTYPE, t1.VALS
> surveys-> FROM sources t0 ,TS t1 where
> surveys-> (t0.SURVEYID = 16 AND t0.SRCID >= 203510110032281 AND
> t0.SRCID <= 203520107001677 and t0.SURVEYID = t1.SURVEY_PK AND t0.SRCID =
> t1.SOURCE_PK ) ORDER BY t0.SURVEYID ASC, t0.SRCID ASC
We don't make any attempt to infer derived inequality conditions,
so no, those constraints on t0.srcid won't be propagated over to
t1.source_pk. Sorry. It's been suggested before, but it would be
a lot of new mechanism and expense in the planner, and for most
queries it'd just slow things down to try to do that.
> I have around 30 clients running the same query with different
> parameters, but the query always returns 1000 rows (boundary values
> are pre-calculated,so it's like traversal of the equiwidth histogram
> if it comes to srcid/source_pk) and the rows from parallel queries
> cannot be overlapping. Usually query returns within around a second.
> I noticed however there are some queries that hang for many hours and
> what is most curious some of them created several GB of temp files.
Can you show us the query plan for the slow cases?
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@cern.ch, pgsql-bugs@postgresql.org
Subject: Re: Query causing explosion of temp space with join involving partitioning
In-Reply-To: <7409.1274383309@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