To: krzysztof.nienartowicz@cern.ch
cc: pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Query causing explosion of temp space with join involving
	partitioning
In-reply-to: <AANLkTine_e2T4lpFfPMvneOtDyhA4u2eQCvtnMdcQb-y@mail.gmail.com> 
References: <AANLkTine_e2T4lpFfPMvneOtDyhA4u2eQCvtnMdcQb-y@mail.gmail.com>
Comments: In-reply-to Krzysztof Nienartowicz
	<krzysztof.nienartowicz.cern@gmail.com>
	message dated "Thu, 20 May 2010 16:00:03 +0200"
Date: Thu, 20 May 2010 15:21:49 -0400
Message-ID: <7409.1274383309@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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