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
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 |
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? |