Re: OUTER JOIN performance regression remains in 8.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 in 8.3beta4
Date: 2008-01-04 22:51:27
Message-ID: 477E640F.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2008-01-04 23:29:30 Re: OUTER JOIN performance regression remains in 8.3beta4
Previous Message Tom Lane 2008-01-04 22:46:59 Re: OUTER JOIN performance regression remains in 8.3beta4

Browse pgsql-patches by date

  From Date Subject
Next Message Kevin Grittner 2008-01-04 23:29:30 Re: OUTER JOIN performance regression remains in 8.3beta4
Previous Message Tom Lane 2008-01-04 22:46:59 Re: OUTER JOIN performance regression remains in 8.3beta4