Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id C88E7632979 for ; Thu, 27 May 2010 13:41:41 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 74564-09; Thu, 27 May 2010 16:41:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 4F0CE6322DE; Thu, 27 May 2010 13:41:30 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o4RGfS7E003579; Thu, 27 May 2010 12:41:29 -0400 (EDT) To: Krzysztof Nienartowicz cc: pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Query causing explosion of temp space with join involving partitioning In-reply-to: References: Comments: In-reply-to Krzysztof Nienartowicz message dated "Wed, 26 May 2010 17:27:50 +0200" Date: Thu, 27 May 2010 12:41:28 -0400 Message-ID: <3578.1274978488@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/243 X-Sequence-Number: 26905 Krzysztof Nienartowicz writes: > Logs of the system running queries are not utterly clear, so chasing the > parameters for the explosive query is not that simple (shared logs between > multiple threads), but from what I see there is no difference between them > and the plan looks like (without removal of irrelevant parameters this time, > most of them are float8, but also bytea) > [ nestloop with inner index scans over the inherited table ] Well, that type of plan isn't going to consume much memory or disk space. What I suspect is happening is that sometimes, depending on the specific parameter values called out in the query, the planner is switching to another plan type that does consume lots of space (probably via sort or hash temp files). The most obvious guess is that that will happen when the range limits on srcid get far enough apart to make a nestloop not look cheap. You could try experimenting with EXPLAIN and different constant values to see what you get. regards, tom lane