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

Re: EXISTS optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org, "Andrea Olson" <Andrea(dot)Olson(at)wicourts(dot)gov>, "Bill Severson" <Bill(dot)Severson(at)wicourts(dot)gov>, "John Hutchins" <John(dot)Hutchins(at)wicourts(dot)gov>, "Randy Peterson" <Randy(dot)Peterson(at)wicourts(dot)gov>, "Shannon Spranger" <Shannon(dot)Spranger(at)wicourts(dot)gov>
Subject: Re: EXISTS optimization
Date: 2007-03-23 21:49:42
Message-ID: 25339.1174686582@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-03-23 22:13:52
Subject: Re: Strange left outer join performance issue
Previous:From: Noah M. DanielsDate: 2007-03-23 21:16:48
Subject: Re: Strange left outer join performance issue

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2007-03-23 21:57:40
Subject: Re: [COMMITTERS] pgsql: We no longer need to palloc the VacuumStmt node; keeping it on
Previous:From: Tom LaneDate: 2007-03-23 21:22:22
Subject: Re: [COMMITTERS] pgsql: We no longer need to palloc the VacuumStmt node; keeping it on

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