OUTER JOIN performance regression remains in 8.3beta4

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: OUTER JOIN performance regression remains in 8.3beta4
Date: 2008-01-04 18:16:13
Message-ID: 477E238D.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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

Attachment Content-Type Size
ChargeSQ-explain-analyze-8.3beta4.txt text/plain 4.6 KB
ChargeSQ-explain-analyze-8.2.4.txt text/plain 4.3 KB
ChargeSQ-inner-join-explain-analyze-8.3beta4.txt text/plain 3.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Richard Huxton 2008-01-04 18:29:07 Re: Problem with PgTcl auditing function on trigger
Previous Message Andrew Sullivan 2008-01-04 18:11:08 Re: Index performance

Browse pgsql-patches by date

  From Date Subject
Next Message Aidan Van Dyk 2008-01-04 18:36:52 Re: SSL over Unix-domain sockets
Previous Message Bruce Momjian 2008-01-04 17:57:28 Re: SSL over Unix-domain sockets