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

Re: OUTER JOIN performance regression remains in8.3beta4

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Date: 2008-01-04 22:51:27
Message-ID: 477E640F.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
>>> On Fri, Jan 4, 2008 at  4:46 PM, in message <24033(dot)1199486819(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
 
> I don't see anything in that thread that shows the view definition
 
It was in the first post on the other thread, but for convenience:
 
bigbird=# \d "CaseTypeHistEvent"
      View "public.CaseTypeHistEvent"
     Column     |     Type      | Modifiers
----------------+---------------+-----------
 caseType       | "CaseTypeT"   |
 eventType      | "EventTypeT"  |
 descr          | "EventDescrT" |
 isActive       | boolean       |
 isKeyEvent     | boolean       |
 isMoneyEnabled | boolean       |
 keyEventSeqNo  | integer       |
 countyNo       | "CountyNoT"   |
View definition:
 SELECT b."caseType", b."eventType", b.descr, b."isActive",
        CASE
            WHEN d."eventType" IS NOT NULL THEN d."isKeyEvent"
            ELSE b."isKeyEvent"
        END AS "isKeyEvent",
        CASE
            WHEN d."eventType" IS NOT NULL THEN d."isMoneyEnabled"
            ELSE b."isMoneyEnabled"
        END AS "isMoneyEnabled", COALESCE(
        CASE
            WHEN d."eventType" IS NOT NULL THEN d."keyEventSeqNo"::smallint
            ELSE b."keyEventSeqNo"::smallint
        END::integer, 0) AS "keyEventSeqNo", c."countyNo"
   FROM ONLY "CaseTypeHistEventB" b
   JOIN ONLY "ControlRecord" c ON 1 = 1
   LEFT JOIN ONLY "CaseTypeHistEventD" d ON d."caseType"::bpchar = b."caseType"::bpchar AND d."eventType"::bpchar = b."eventType"::bpchar AND d."countyNo"::smallint = c."countyNo"::smallint;
 
> nor mentions the data types involved.
 
I'll pull those together and post shortly.  All are domains
without modifiers or checks.
 
-Kevin
 

 

In response to

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2008-01-04 23:29:30
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Previous:From: Tom LaneDate: 2008-01-04 22:46:59
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4

pgsql-patches by date

Next:From: Kevin GrittnerDate: 2008-01-04 23:29:30
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Previous:From: Tom LaneDate: 2008-01-04 22:46:59
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4

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