X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0E6929DC876; Tue, 28 Feb 2006 12:52:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63078-03; Tue, 28 Feb 2006 12:52:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 60FC69DC847; Tue, 28 Feb 2006 12:52:11 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1SGqBME015925; Tue, 28 Feb 2006 11:52:11 -0500 (EST) To: "Kevin Grittner" cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] temporary indexes In-reply-to: <44041B68.EE98.0025.0@wicourts.gov> References: <44041B68.EE98.0025.0@wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Tue, 28 Feb 2006 09:44:08 -0600" Date: Tue, 28 Feb 2006 11:52:11 -0500 Message-ID: <15924.1141145531@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.111 required=5 tests=[AWL=0.111] X-Spam-Score: 0.111 X-Spam-Level: X-Archive-Number: 200602/1210 X-Sequence-Number: 80348 "Kevin Grittner" writes: > It struck me that it would be outstanding if the planner could > recognize this sort of situation, and build a temporary index based on > the snapshot of the data visible to the transaction. I don't think that's an appropriate solution at all. What it looks like to me (assuming that explain's estimated row counts are reasonably on-target) is that the time is all going into the EXISTS subplans. The real problem here is that we aren't doing anything to convert correlated EXISTS subqueries into some form of join that's smarter than a raw nestloop. regards, tom lane