>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> If you're still interested in testing CVS HEAD's handling of EXISTS,
> I've about finished what I wanted to do with it.
It's been hectic here, but I've managed to let some stuff run in the
background using an old test case from here:
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01408.php
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%'
)
;
On development machine using 8.3.3:
Nested Loop (cost=0.00..399190.49 rows=1 width=37) (actual
time=7184.068..3249391.592 rows=12372 loops=1)
Join Filter: (("A"."adjustmentNo")::text = ("H"."tranId")::text)
-> Seq Scan on "Adjustment" "A" (cost=0.00..5218.87 rows=247869
width=17) (actual time=9.804..1695.691 rows=248674 loops=1)
Filter: (((date)::date > '2006-01-01'::date) AND
(("countyNo")::smallint = 66))
-> Index Scan using "TranHeader_pkey" on "TranHeader" "H"
(cost=0.00..1.57 rows=1 width=37) (actual time=13.056..13.056 rows=0
loops=248674)
Index Cond: ((("H"."tranNo")::integer =
("A"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
Filter: ((("H"."tranType")::text = 'A'::text) AND (subplan))
SubPlan
-> Index Scan using "TranDetail_TranDetCaseNo" on
"TranDetail" "D" (cost=0.00..1.29 rows=1 width=0) (actual
time=13.017..13.017 rows=0 loops=248674)
Index Cond: ((("caseNo")::text >= '2006TR'::text) AND
(("caseNo")::text < '2006TS'::text) AND (("tranNo")::integer =
($0)::integer) AND (("countyNo")::smallint = ($1)::smallint))
Filter: (("caseNo")::text ~~ '2006TR%'::text)
Total runtime: 3249404.662 ms
On the same machine, using the snapshot from this morning:
Nested Loop (cost=1963.24..38483.54 rows=1 width=37) (actual
time=372.964..986.994 rows=12372 loops=1)
Join Filter: (("H"."tranNo")::integer = ("A"."tranNo")::integer)
-> Merge Semi Join (cost=1963.24..31012.28 rows=21317 width=37)
(actual time=372.926..839.298 rows=12372 loops=1)
Merge Cond: (("H"."tranNo")::integer =
("D"."tranNo")::integer)
Join Filter: (("D"."countyNo")::smallint =
("H"."countyNo")::smallint)
-> Index Scan using "TranHeader_pkey" on "TranHeader" "H"
(cost=0.00..27848.57 rows=322517 width=37) (actual time=3.722..526.124
rows=311963 loops=1
)
Index Cond: (("countyNo")::smallint = 66)
Filter: (("tranType")::text = 'A'::text)
-> Sort (cost=1963.17..2027.08 rows=25565 width=6) (actual
time=171.512..191.688 rows=76597 loops=1)
Sort Key: "D"."tranNo"
Sort Method: quicksort Memory: 6663kB
-> Index Scan using "TranDetail_TranDetCaseNo" on
"TranDetail" "D" (cost=0.00..91.57 rows=25565 width=6) (actual
time=0.031..100.688 rows=7659
7 loops=1)
Index Cond: ((("caseNo")::text >= '2006TR'::text)
AND (("caseNo")::text < '2006TS'::text) AND (("countyNo")::smallint =
66))
Filter: (("caseNo")::text ~~ '2006TR%'::text)
-> Index Scan using "Adjustment_pkey" on "Adjustment" "A"
(cost=0.00..0.34 rows=1 width=17) (actual time=0.009..0.010 rows=1
loops=12372)
Index Cond: ((("A"."adjustmentNo")::text =
("H"."tranId")::text) AND (("A"."countyNo")::smallint = 66))
Filter: (("A".date)::date > '2006-01-01'::date)
Total runtime: 991.097 ms
The chosen plan looks very reasonable, and performs very well. Nice!
After converting the database I originally forgot to run VACUUM
ANALYZE. Even planning "blind" and doing hint-bit rewrites it picked
a plan which ran in under 10 seconds.
I'll be running other tests as I get the chance.
-Kevin