There was a serious performance regression in OUTER JOIN planning
going from 8.2.4 to 8.2.5. I know Tom came up with some patches to
mitigate the issues in 8.2.5, but my testing shows that problems
remain in 8.3beta4.
The query:
SELECT
"CH"."caseNo",
"CH"."countyNo",
"CH"."chargeNo",
"CH"."statuteCite",
"CH"."sevClsCode",
"CH"."modSevClsCode",
"S"."descr" AS "sevClsCodeDescr",
"M"."descr" AS "modSevClsCodeDescr",
"CH"."descr",
"CH"."offenseDate",
"CH"."pleaCode",
"PC"."descr" AS "pleaCodeDescr",
"CH"."pleaDate",
"CH"."chargeSeqNo",
"CHST"."eventDate" AS "reopEventDate",
"CTHE"."descr" AS "reopEventDescr"
FROM "Charge" "CH"
LEFT OUTER JOIN "SevClsCode" "S" ON ("S"."sevClsCode" = "CH"."sevClsCode")
LEFT OUTER JOIN "SevClsCode" "M" ON ("M"."sevClsCode" = "CH"."modSevClsCode")
LEFT OUTER JOIN "PleaCode" "PC" ON ("PC"."pleaCode" = "CH"."pleaCode")
LEFT OUTER JOIN "CaseHist" "CHST"
ON ( "CHST"."countyNo" = "CH"."countyNo"
AND "CHST"."caseNo" = "CH"."caseNo"
AND "CHST"."histSeqNo" = "CH"."reopHistSeqNo"
)
LEFT OUTER JOIN "CaseTypeHistEvent" "CTHE"
ON ( "CHST"."eventType" = "CTHE"."eventType"
AND "CHST"."caseType" = "CTHE"."caseType"
AND "CHST"."countyNo" = "CTHE"."countyNo"
)
WHERE "CH"."caseNo" = '2007CM003476'
AND "CH"."countyNo" = 53
ORDER BY
"chargeNo",
"chargeSeqNo"
;
The attached EXPLAIN ANALYZE results show:
(1) A run of the above under 8.3beta4.
(2) A run of the above under 8.2.4.
(3) A run of the above with all OUTER JOINs changed to INNER under 8.3beta4.
-Kevin
Sort (cost=101161.84..101161.85 rows=4 width=226) (actual
time=113110.725..113110.731 rows=4 loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
Sort Method: quicksort Memory: 18kB
-> Hash Left Join (cost=82004.90..101161.80 rows=4 width=226) (actual
time=110701.127..113110.637 rows=4 loops=1)
Hash Cond: (("CH"."sevClsCode")::bpchar = ("S"."sevClsCode")::bpchar)
-> Hash Left Join (cost=82003.36..101160.20 rows=4 width=195)
(actual time=110701.005..113110.503 rows=4 loops=1)
Hash Cond: (("CH"."modSevClsCode")::bpchar =
("M"."sevClsCode")::bpchar)
-> Hash Left Join (cost=82001.82..101158.64 rows=4 width=164)
(actual time=110700.858..113110.345 rows=4 loops=1)
Hash Cond: (("CH"."pleaCode")::bpchar =
("PC"."pleaCode")::bpchar)
-> Hash Left Join (cost=82000.60..101157.37 rows=4
width=128) (actual time=110700.720..113110.191 rows=4 loops=1)
Hash Cond: ((("CHST"."eventType")::bpchar =
("CTHE"."eventType")::bpchar) AND (("CHST"."caseType")::bpchar =
("CTHE"."caseType")::bpchar) AND (("CHST"."countyNo")::smallint =
("CTHE"."countyNo")::smallint))
-> Nested Loop Left Join (cost=0.00..87.70 rows=4
width=107) (actual time=0.189..0.241 rows=4 loops=1)
-> Index Scan using "Charge_pkey" on "Charge"
"CH" (cost=0.00..10.32 rows=4 width=94) (actual time=0.174..0.186 rows=4
loops=1)
Index Cond: ((("countyNo")::smallint =
53) AND (("caseNo")::bpchar = '2007CM003476'::bpchar))
-> Index Scan using "CaseHist_pkey" on
"CaseHist" "CHST" (cost=0.00..19.32 rows=2 width=32) (actual time=0.002..0.002
rows=0 loops=4)
Index Cond:
((("CHST"."countyNo")::smallint = 53) AND (("CHST"."caseNo")::bpchar =
'2007CM003476'::bpchar) AND (("CHST"."histSeqNo")::smallint =
("CH"."reopHistSeqNo")::smallint))
-> Hash (cost=44597.18..44597.18 rows=1146024
width=98) (actual time=110700.290..110700.290 rows=1146024 loops=1)
-> Subquery Scan "CTHE"
(cost=148.78..44597.18 rows=1146024 width=98) (actual time=14.673..92707.265
rows=1146024 loops=1)
-> Merge Left Join
(cost=148.78..33136.94 rows=1146024 width=77) (actual time=14.668..72106.330
rows=1146024 loops=1)
Merge Cond:
(((b."caseType")::bpchar = (d."caseType")::bpchar) AND ((b."eventType")::bpchar
= (d."eventType")::bpchar))
Join Filter:
((d."countyNo")::smallint = (c."countyNo")::smallint)
-> Nested Loop
(cost=2.79..23557.55 rows=1146024 width=67) (actual time=0.114..32623.571
rows=1146024 loops=1)
-> Index Scan using
"CaseTypeHistEventB_pkey" on "CaseTypeHistEventB" b (cost=0.00..634.28
rows=15917 width=65) (actual time=0.071..2405.293 rows=15917 loops=1)
-> Materialize
(cost=2.79..3.51 rows=72 width=2) (actual time=0.002..0.516 rows=72 loops=15917)
-> Seq Scan on
"ControlRecord" c (cost=0.00..2.72 rows=72 width=2) (actual time=0.031..0.183
rows=72 loops=1)
-> Sort (cost=145.99..151.14
rows=2060 width=15) (actual time=14.530..207.873 rows=148249 loops=1)
Sort Key: d."caseType",
d."eventType"
Sort Method: quicksort
Memory: 145kB
-> Seq Scan on
"CaseTypeHistEventD" d (cost=0.00..32.60 rows=2060 width=15) (actual
time=0.039..3.364 rows=2060 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=41) (actual
time=0.061..0.061 rows=10 loops=1)
-> Seq Scan on "PleaCode" "PC" (cost=0.00..1.10
rows=10 width=41) (actual time=0.023..0.037 rows=10 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=34) (actual
time=0.106..0.106 rows=24 loops=1)
-> Seq Scan on "SevClsCode" "M" (cost=0.00..1.24 rows=24
width=34) (actual time=0.024..0.057 rows=24 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=34) (actual time=0.087..0.087
rows=24 loops=1)
-> Seq Scan on "SevClsCode" "S" (cost=0.00..1.24 rows=24
width=34) (actual time=0.006..0.039 rows=24 loops=1)
Total runtime: 113114.552 ms
Sort (cost=2533.51..2533.53 rows=5 width=253) (actual time=435.412..435.416
rows=4 loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
-> Hash Left Join (cost=2390.88..2533.46 rows=5 width=253) (actual
time=435.254..435.327 rows=4 loops=1)
Hash Cond: (("CH"."sevClsCode")::bpchar = ("S"."sevClsCode")::bpchar)
-> Hash Left Join (cost=2389.34..2531.85 rows=5 width=219) (actual
time=435.146..435.209 rows=4 loops=1)
Hash Cond: (("CH"."modSevClsCode")::bpchar =
("M"."sevClsCode")::bpchar)
-> Hash Left Join (cost=2387.80..2530.28 rows=5 width=185)
(actual time=428.365..428.420 rows=4 loops=1)
Hash Cond: (("CH"."pleaCode")::bpchar =
("PC"."pleaCode")::bpchar)
-> Hash Left Join (cost=2386.57..2529.00 rows=5
width=146) (actual time=421.921..421.967 rows=4 loops=1)
Hash Cond: ((("CHST"."eventType")::bpchar =
("CTHE"."eventType")::bpchar) AND (("CHST"."caseType")::bpchar =
("CTHE"."caseType")::bpchar))
-> Nested Loop Left Join (cost=0.00..122.50 rows=5
width=131) (actual time=4.777..4.814 rows=4 loops=1)
-> Index Scan using "Charge_pkey" on "Charge"
"CH" (cost=0.00..12.17 rows=5 width=112) (actual time=4.765..4.775 rows=4
loops=1)
Index Cond: ((("countyNo")::smallint =
53) AND (("caseNo")::bpchar = '2007CM003476'::bpchar))
-> Index Scan using "CaseHist_pkey" on
"CaseHist" "CHST" (cost=0.00..22.04 rows=2 width=41) (actual time=0.002..0.002
rows=0 loops=4)
Index Cond:
((("CHST"."countyNo")::smallint = 53) AND (("CHST"."caseNo")::bpchar =
'2007CM003476'::bpchar) AND (("CHST"."histSeqNo")::smallint =
("CH"."reopHistSeqNo")::smallint))
-> Hash (cost=2147.82..2147.82 rows=15917
width=98) (actual time=417.096..417.096 rows=15917 loops=1)
-> Subquery Scan "CTHE"
(cost=1684.48..2147.82 rows=15917 width=98) (actual time=293.804..388.572
rows=15917 loops=1)
-> Merge Right Join
(cost=1684.48..1988.65 rows=15917 width=89) (actual time=293.801..358.342
rows=15917 loops=1)
Merge Cond:
(((d."countyNo")::smallint = "inner"."?column9?") AND ((d."caseType")::bpchar =
"inner"."?column10?") AND ((d."eventType")::bpchar = "inner"."?column11?"))
-> Index Scan using
"CaseTypeHistEventD_pkey" on "CaseTypeHistEventD" d (cost=0.00..89.64
rows=2066 width=21) (actual time=9.970..36.191 rows=1460 loops=1)
-> Sort (cost=1684.48..1724.27
rows=15917 width=76) (actual time=255.997..271.497 rows=15917 loops=1)
Sort Key:
(c."countyNo")::smallint, (b."caseType")::bpchar, (b."eventType")::bpchar
-> Nested Loop
(cost=0.00..573.61 rows=15917 width=76) (actual time=9.941..88.116 rows=15917
loops=1)
-> Index Scan using
"ControlRecord_pkey" on "ControlRecord" c (cost=0.00..4.27 rows=1 width=2)
(actual time=0.062..0.069 rows=1 loops=1)
Index Cond:
(("countyNo")::smallint = 53)
-> Seq Scan on
"CaseTypeHistEventB" b (cost=0.00..410.17 rows=15917 width=74) (actual
time=9.871..45.731 rows=15917 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=47) (actual
time=6.413..6.413 rows=10 loops=1)
-> Seq Scan on "PleaCode" "PC" (cost=0.00..1.10
rows=10 width=47) (actual time=6.383..6.394 rows=10 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=40) (actual
time=6.747..6.747 rows=24 loops=1)
-> Seq Scan on "SevClsCode" "M" (cost=0.00..1.24 rows=24
width=40) (actual time=6.652..6.678 rows=24 loops=1)
-> Hash (cost=1.24..1.24 rows=24 width=40) (actual time=0.081..0.081
rows=24 loops=1)
-> Seq Scan on "SevClsCode" "S" (cost=0.00..1.24 rows=24
width=40) (actual time=0.004..0.030 rows=24 loops=1)
Total runtime: 444.599 ms
Sort (cost=44.56..44.57 rows=1 width=245) (actual time=0.196..0.196 rows=0
loops=1)
Sort Key: "CH"."chargeNo", "CH"."chargeSeqNo"
Sort Method: quicksort Memory: 17kB
-> Nested Loop Left Join (cost=1.54..44.55 rows=1 width=245) (actual
time=0.182..0.182 rows=0 loops=1)
-> Nested Loop (cost=1.54..40.26 rows=1 width=256) (actual
time=0.180..0.180 rows=0 loops=1)
-> Nested Loop (cost=1.54..37.35 rows=1 width=256) (actual
time=0.178..0.178 rows=0 loops=1)
Join Filter: (("CH"."sevClsCode")::bpchar =
("S"."sevClsCode")::bpchar)
-> Nested Loop (cost=1.54..35.81 rows=1 width=225)
(actual time=0.175..0.175 rows=0 loops=1)
Join Filter: (("CH"."pleaCode")::bpchar =
("PC"."pleaCode")::bpchar)
-> Nested Loop (cost=1.54..34.59 rows=1 width=189)
(actual time=0.172..0.172 rows=0 loops=1)
-> Nested Loop (cost=1.54..31.23 rows=1
width=138) (actual time=0.170..0.170 rows=0 loops=1)
-> Hash Join (cost=1.54..11.88 rows=1
width=125) (actual time=0.168..0.168 rows=0 loops=1)
Hash Cond:
(("CH"."modSevClsCode")::bpchar = ("M"."sevClsCode")::bpchar)
-> Index Scan using "Charge_pkey"
on "Charge" "CH" (cost=0.00..10.32 rows=4 width=94) (actual time=0.042..0.050
rows=4 loops=1)
Index Cond:
((("countyNo")::smallint = 53) AND (("caseNo")::bpchar =
'2007CM003476'::bpchar))
-> Hash (cost=1.24..1.24 rows=24
width=34) (actual time=0.096..0.096 rows=24 loops=1)
-> Seq Scan on "SevClsCode"
"M" (cost=0.00..1.24 rows=24 width=34) (actual time=0.011..0.044 rows=24
loops=1)
-> Index Scan using "CaseHist_pkey" on
"CaseHist" "CHST" (cost=0.00..19.32 rows=2 width=32) (never executed)
Index Cond:
((("CHST"."countyNo")::smallint = 53) AND (("CHST"."caseNo")::bpchar =
'2007CM003476'::bpchar) AND (("CHST"."histSeqNo")::smallint =
("CH"."reopHistSeqNo")::smallint))
-> Index Scan using "CaseTypeHistEventB_pkey"
on "CaseTypeHistEventB" b (cost=0.00..3.34 rows=1 width=60) (never executed)
Index Cond: (((b."caseType")::bpchar =
("CHST"."caseType")::bpchar) AND ((b."eventType")::bpchar =
("CHST"."eventType")::bpchar))
-> Seq Scan on "PleaCode" "PC" (cost=0.00..1.10
rows=10 width=41) (never executed)
-> Seq Scan on "SevClsCode" "S" (cost=0.00..1.24 rows=24
width=34) (never executed)
-> Seq Scan on "ControlRecord" c (cost=0.00..2.90 rows=1
width=2) (never executed)
Filter: ((c."countyNo")::smallint = 53)
-> Index Scan using "CaseTypeHistEventD_pkey" on "CaseTypeHistEventD"
d (cost=0.00..4.27 rows=1 width=11) (never executed)
Index Cond: (((d."countyNo")::smallint =
(c."countyNo")::smallint) AND ((d."caseType")::bpchar = (b."caseType")::bpchar)
AND ((d."eventType")::bpchar = (b."eventType")::bpchar))
Total runtime: 0.747 ms
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
