OUTER JOIN performance regression remains in 8.3beta4

Поиск
Список
Период
Сортировка
От 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  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Re: OUTER JOIN performance regression remains in 8.3beta4  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: OUTER JOIN performance regression remains in 8.3beta4  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Andrew Sullivan
Дата:
Сообщение: Re: Index performance
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Problem with PgTcl auditing function on trigger