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

Re: [PERFORM] EXISTS optimization

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Kevin Grittner" <Kgrittn(dot)CCAP(dot)Courts(at)wicourts(dot)gov>
Cc: <pgsql-hackers(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>,"Andrea Olson" <Aolson(dot)CCAP(dot)Courts(at)wicourts(dot)gov>,"Bill Severson" <BSEVERS(dot)CCAP(dot)Courts(at)wicourts(dot)gov>,"John Hutchins" <jhutchi(dot)CCAP(dot)Courts(at)wicourts(dot)gov>,"Randy Peterson" <RPETERS(dot)CCAP(dot)Courts(at)wicourts(dot)gov>,"Shannon Spranger" <ssprang(dot)CCAP(dot)Courts(at)wicourts(dot)gov>
Subject: Re: [PERFORM] EXISTS optimization
Date: 2007-03-23 22:37:16
Message-ID: 4604104C.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance

>>> On Fri, Mar 23, 2007 at  5:26 PM, in message
<46040DAC(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote: 

> I tried something which seems 
> equivalent, but it is running for a very long time.  I'll show it with just 
> the explain while I wait to see how long the explain analyze takes.
>  
> explain
> 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 "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" = "H"."countyNo"
>         )
> ;

explain analyze results:
 
 Nested Loop  (cost=0.00..181673.08 rows=1 width=46) (actual time=42224.077..964266.969 rows=2209 loops=1)
   Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
   ->  Seq Scan on "Adjustment" "A"  (cost=0.00..2384.27 rows=11733 width=22) (actual time=15.355..146.620 rows=13003 loops=1)
         Filter: (((date)::date > '2006-01-01'::date) AND (("countyNo")::smallint = 66))
   ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..15.27 rows=1 width=46) (actual time=74.141..74.141 rows=0 loops=13003)
         Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
         Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
         SubPlan
           ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..27.66 rows=20 width=4) (actual time=0.039..58.234 rows=42342 loops=13003)
                 Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar) AND (("countyNo")::smallint = ($0)::smallint))
                 Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
 Total runtime: 964269.555 ms


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2007-03-23 23:04:12
Subject: Re: EXISTS optimization
Previous:From: Kevin GrittnerDate: 2007-03-23 22:30:27
Subject: Re: EXISTS optimization

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-23 23:04:12
Subject: Re: EXISTS optimization
Previous:From: Kevin GrittnerDate: 2007-03-23 22:30:27
Subject: Re: EXISTS optimization

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