>>> Tom Lane <[EMAIL PROTECTED]> wrote:
> I believe that the optimizable cases for EXISTS are those where the
> EXISTS() is either at the top level of WHERE, or just underneath a
NOT,
The rest of the plan makes sense to me, but this part seems narrow.
There's probably a good reason for that which is beyond my depth, but
attached is a view that is used for calculating statistics for a
database which is primarily used for "case management" purposes. If
EXISTS could also be optimized in the contexts used there, it would be
great.
For context, this view references three other non-trivial views
(MatterHistSearch, MatterHistStage, & MatterHistStatus), and does
perform acceptably, so it's not a matter of complaining if it can't
work here, just providing a real-world example of other useful
contexts for EXISTS which might be worth covering if possible.
This view is used in a large number of queries, mostly either
selecting a single date with other selection criteria or counting rows
which match a set of matterNo values selected on complex criteria.
By the way, this view was totally unusable under 8.2. Showing how it
worked under 8.3 was all it took to get them to expedite the upgrade.
These views, possible because of improvements in 8.3, saved "countless
programmer days" (to quote one of the programmers).
-Kevin
CREATE VIEW "MatterDateStat" AS
SELECT
"S"."matterNo",
CAST("D"."date" AS "DateT") AS "date",
"S"."newStageCode" AS "stage",
CAST(COALESCE("O"."newStatusCode", 'OP') AS "MatterStatusCodeT") AS
"status",
(
EXISTS
(
SELECT *
FROM "MatterHistSearch" "MHS1"
JOIN "MatterEventCode" "MEC1"
ON ("MEC1"."matterEventCode" = "MHS1"."matterEventCode")
WHERE "MHS1"."matterNo" = "S"."matterNo"
AND "MHS1"."date" <= "D"."date"
AND "MEC1"."newMaintCode" = 'INA'
AND NOT EXISTS
(
SELECT *
FROM "MatterHistSearch" "MHS2"
JOIN "MatterEventCode" "MEC2"
ON ("MEC2"."matterEventCode" = "MHS2"."matterEventCode")
WHERE "MHS2"."matterNo" = "S"."matterNo"
AND "MHS2"."date" <= "D"."date"
AND ("MHS2"."date", "MHS2"."matterHistRowOrder") >
("MHS1"."date", "MHS1"."matterHistRowOrder")
AND "MEC2"."removeMaintCode" = 'INA'
)
)
) AS "isOnHold",
"S"."parentMatter",
"S"."matterHistSeqNo", -- NULL means that the stage is defaulting from the
matter filing date.
"S"."areaOfLawCode",
"S"."county",
"S"."enteredDate",
"S"."filedDate",
"S"."grievInvestigator",
"S"."intakeInvestigator",
"S"."isSelfNotification",
"S"."litigationMatterNo",
"S"."matterType",
"S"."oldMatterNo",
"S"."reportMethodCode",
"S"."respondent",
"S"."sccaCaseNo",
"S"."takenBy",
"L"."isPublic",
"L"."matterDispoCode",
"L"."matterDispoDate"
-- Oldest "filedDate" for a matter in the database is used. (Older ones not
likely to appear now.)
FROM (SELECT DATE '1974-05-15' + generate_series(0, (CURRENT_DATE - DATE
'1974-05-15')) AS "date") "D"
JOIN "MatterHistStage" "S"
ON ( "S"."date" <= "D"."date"
AND NOT EXISTS
(
SELECT * FROM "MatterHistStage" "S2"
WHERE "S2"."matterNo" = "S"."matterNo"
AND "S2"."date" <= "D"."date"
AND ("S2"."date", "S2"."matterHistRowOrder") > ("S"."date",
"S"."matterHistRowOrder")
)
)
JOIN "Matter" "L" -- Litigation matter, if present; otherwise the original
matter.
ON ("L"."matterNo" = COALESCE("S"."litigationMatterNo", "S"."matterNo"))
LEFT JOIN "MatterHistStatus" "O"
ON ( "O"."matterNo" = "S"."matterNo"
AND "O"."date" <= "D"."date"
AND NOT EXISTS
(
SELECT * FROM "MatterHistStatus" "O2"
WHERE "O2"."matterNo" = "O"."matterNo"
AND "O2"."date" <= "D"."date"
AND ("O2"."date", "O2"."matterHistRowOrder") > ("O"."date",
"O"."matterHistRowOrder")
)
)
WHERE COALESCE("O"."newStatusCode", 'OP') <> 'CL'
;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
