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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: krzysztof(dot)nienartowicz(at)cern(dot)ch
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-20 19:21:49
Message-ID: 7409.1274383309@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-performance

Krzysztof Nienartowicz <krzysztof(dot)nienartowicz(dot)cern(at)gmail(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2010-05-20 20:34:01 Re: BUG #5467: wrong classification at index
Previous Message KOIZUMI Satoru 2010-05-20 16:57:59 BUG #5467: wrong classification at index

Browse pgsql-performance by date

  From Date Subject
Next Message David Jarvis 2010-05-20 19:45:53 Re: Optimize date query for large child tables: GiST or GIN?
Previous Message Thom Brown 2010-05-20 19:21:30 Re: Optimize date query for large child tables: GiST or GIN?