Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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: ChargeSQ-inner-join-explain-analyze-8.3beta4.txt
Description: text/plain (3.3 KB)
Attachment: ChargeSQ-explain-analyze-8.2.4.txt
Description: text/plain (4.3 KB)
Attachment: ChargeSQ-explain-analyze-8.3beta4.txt
Description: text/plain (4.6 KB)

Responses

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group