Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-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

pgsql-performance by date

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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group