Re: Planner reluctant to start from subquery

>>> On Wed, Feb 1, 2006 at 2:43 pm, in message
<43E0C8F5(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
> I took out the OR in the
> where clause, without eliminating that last outer join, and it
optimized
> fine.

FYI, with both sides of the OR separated:

explain analyze
SELECT "C".*, "P"."partyNo"
FROM "Case" "C"
JOIN "Party" "P" ON ("C"."caseNo" = "P"."caseNo" AND "C"."countyNo" =
"P"."countyNo")
LEFT OUTER JOIN "WccaPermCaseType" "WPCT"
ON ( "C"."caseType" = "WPCT"."caseType"
AND "C"."countyNo" = "WPCT"."countyNo"
AND "WPCT"."profileName" = 'PUBLIC'
)
WHERE "WPCT"."profileName" IS NOT NULL
AND "C"."countyNo" = 66
AND "C"."caseNo" IN
(
SELECT "D"."caseNo"
FROM "DocImageMetaData" "D"
WHERE "D"."isEFiling" = true
AND "D"."insertedDate" BETWEEN '2006-01-01' AND '2006-01-07'
AND "D"."countyNo" = 66
)
ORDER BY
"caseNo"
;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2321.48..2321.48 rows=1 width=210) (actual
time=5.908..6.001 rows=51 loops=1)
Sort Key: "C"."caseNo"
-> Nested Loop (cost=2309.94..2321.47 rows=1 width=210) (actual
time=3.407..5.605 rows=51 loops=1)
-> Nested Loop (cost=2309.94..2316.98 rows=1 width=226)
(actual time=3.353..4.659 rows=22 loops=1)
-> Nested Loop (cost=2309.94..2313.50 rows=1
width=226) (actual time=3.301..4.023 rows=22 loops=1)
-> HashAggregate (cost=2309.94..2309.95 rows=1
width=18) (actual time=3.251..3.300 rows=22 loops=1)
-> Index Scan using
"DocImageMetaData_CountyNoInsertedDate" on "DocImageMetaData" "D"
(cost=0.00..2309.93 rows=6 width=18) (actual time=0.681..3.141 rows=29
loops=1)
Index Cond: ((("countyNo")::smallint =
66) AND (("insertedDate")::date >= '2006-01-01'::date) AND
(("insertedDate")::date <= '2006-01-07'::date))
Filter: "isEFiling"
-> Index Scan using "Case_pkey" on "Case" "C"
(cost=0.00..3.53 rows=1 width=208) (actual time=0.018..0.020 rows=1
loops=22)
Index Cond: ((("C"."countyNo")::smallint =
66) AND (("C"."caseNo")::bpchar = ("outer"."caseNo")::bpchar))
-> Index Scan using "WccaPermCaseType_ProfileName" on
"WccaPermCaseType" "WPCT" (cost=0.00..3.47 rows=1 width=8) (actual
time=0.015..0.017 rows=1 loops=22)
Index Cond: ((("WPCT"."profileName")::text =
'PUBLIC'::text) AND (("outer"."caseType")::bpchar =
("WPCT"."caseType")::bpchar) AND (66 = ("WPCT"."countyNo")::smallint))
Filter: ("profileName" IS NOT NULL)
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..4.46 rows=2 width=22) (actual time=0.017..0.025 rows=2
loops=22)
Index Cond: ((66 = ("P"."countyNo")::smallint) AND
(("outer"."caseNo")::bpchar = ("P"."caseNo")::bpchar))
Total runtime: 6.511 ms
(17 rows)

explain analyze
SELECT "C".*, "P"."partyNo"
FROM "Case" "C"
JOIN "Party" "P" ON ("C"."caseNo" = "P"."caseNo" AND "C"."countyNo" =
"P"."countyNo")
LEFT OUTER JOIN "WccaPermCaseType" "WPCT"
ON ( "C"."caseType" = "WPCT"."caseType"
AND "C"."countyNo" = "WPCT"."countyNo"
AND "WPCT"."profileName" = 'PUBLIC'
)
WHERE "C"."caseType" = 'PA' AND "C"."isConfidential" = false
AND "C"."countyNo" = 66
AND "C"."caseNo" IN
(
SELECT "D"."caseNo"
FROM "DocImageMetaData" "D"
WHERE "D"."isEFiling" = true
AND "D"."insertedDate" BETWEEN '2006-01-01' AND '2006-01-07'
AND "D"."countyNo" = 66
)
ORDER BY
"caseNo"
;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=11527.21..11527.21 rows=1 width=210) (actual
time=107.449..107.449 rows=0 loops=1)
Sort Key: "C"."caseNo"
-> Nested Loop IN Join (cost=3.47..11527.20 rows=1 width=210)
(actual time=107.432..107.432 rows=0 loops=1)
-> Hash Left Join (cost=3.47..9637.44 rows=255 width=228)
(actual time=107.425..107.425 rows=0 loops=1)
Hash Cond: ((("outer"."caseType")::bpchar =
("inner"."caseType")::bpchar) AND (("outer"."countyNo")::smallint =
("inner"."countyNo")::smallint))
-> Nested Loop (cost=0.00..9631.40 rows=255 width=228)
(actual time=107.418..107.418 rows=0 loops=1)
-> Index Scan using "Case_CaseTypeStatus" on
"Case" "C" (cost=0.00..4536.25 rows=1136 width=208) (actual
time=107.412..107.412 rows=0 loops=1)
Index Cond: ((("caseType")::bpchar =
'PA'::bpchar) AND (("countyNo")::smallint = 66))
Filter: (NOT "isConfidential")
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..4.46 rows=2 width=22) (never executed)
Index Cond: ((66 =
("P"."countyNo")::smallint) AND (("outer"."caseNo")::bpchar =
("P"."caseNo")::bpchar))
-> Hash (cost=3.47..3.47 rows=1 width=8) (never
executed)
-> Index Scan using
"WccaPermCaseType_ProfileName" on "WccaPermCaseType" "WPCT"
(cost=0.00..3.47 rows=1 width=8) (never executed)
Index Cond: ((("profileName")::text =
'PUBLIC'::text) AND (("caseType")::bpchar = 'PA'::bpchar) AND
(("countyNo")::smallint = 66))
-> Index Scan using "DocImageMetaData_pkey" on
"DocImageMetaData" "D" (cost=0.00..7.40 rows=1 width=18) (never
executed)
Index Cond: ((("D"."countyNo")::smallint = 66) AND
(("outer"."caseNo")::bpchar = ("D"."caseNo")::bpchar))
Filter: ("isEFiling" AND (("insertedDate")::date >=
'2006-01-01'::date) AND (("insertedDate")::date <= '2006-01-07'::date))
Total runtime: 107.860 ms
(18 rows)