| От | Kevin Grittner |
|---|---|
| Тема | OUTER JOIN performance regression remains in 8.3beta4 |
| Дата | |
| Msg-id | 477E238D.EE98.0025.0@wicourts.gov обсуждение |
| Ответы |
Re: OUTER JOIN performance regression remains in
8.3beta4
Re: OUTER JOIN performance regression remains in 8.3beta4 Re: OUTER JOIN performance regression remains in 8.3beta4 |
| Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера