From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org, "Andrea Olson" <Andrea.Olson@wicourts.gov>
Cc: Bill Severson <Bill.Severson@wicourts.gov>
Cc: John Hutchins <John.Hutchins@wicourts.gov>
Cc: Randy Peterson <Randy.Peterson@wicourts.gov>
Cc: Shannon Spranger <Shannon.Spranger@wicourts.gov>
Subject: Re: EXISTS optimization
Date: Fri, 23 Mar 2007 17:49:42 -0400
Message-ID: <25339.1174686582@sss.pgh.pa.us> (raw)
In-Reply-To: <4603C1A3.EE98.0025.0@wicourts.gov>
References: <4603C1A3.EE98.0025.0@wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> explain analyze
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
> FROM "Adjustment" "A"
> JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
> WHERE "H"."tranType" = 'A'
> AND "A"."date" > DATE '2006-01-01'
> AND "H"."countyNo" = 66
> AND "A"."countyNo" = 66
> AND EXISTS
> (
> SELECT 1 FROM "TranDetail" "D"
> WHERE "D"."tranNo" = "H"."tranNo"
> AND "D"."countyNo" = "H"."countyNo"
> AND "D"."caseNo" LIKE '2006TR%'
> )
> ;
> The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses indexed access to the TranHeader and then to Adjustment.
If you want that, try rewriting the EXISTS to an IN:
AND ("H"."tranNo", "H"."countyNo") IN
(
SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
WHERE "D"."caseNo" LIKE '2006TR%'
)
We don't currently try to flatten EXISTS into a unique/join plan as we
do for IN. I seem to recall not doing so when I rewrote IN planning
because I didn't think it would be exactly semantically equivalent,
but that was awhile ago. Right at the moment it seems like it ought
to be equivalent as long as the comparison operators are strict.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, Andrea.Olson@wicourts.gov, Bill.Severson@wicourts.gov, John.Hutchins@wicourts.gov, Randy.Peterson@wicourts.gov, Shannon.Spranger@wicourts.gov
Subject: Re: EXISTS optimization
In-Reply-To: <25339.1174686582@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox