From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner reluctant to start from subquery
Date: Wed, 01 Feb 2006 14:50:51 -0600
Message-ID: <43E0CACA.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <43E0C8F5.EE98.0025.0@wicourts.gov>
References: <43E0AA7E.EE98.0025.0@wicourts.gov>
<43E0AA7E.EE98.0025.0@wicourts.gov>
<3759.1138822464@sss.pgh.pa.us>
<43E0BEDD.EE98.0025.0@wicourts.gov>
<43E0BEDD.EE98.0025.0@wicourts.gov>
<4218.1138824885@sss.pgh.pa.us>
<43E0C4A7.EE98.0025.0@wicourts.gov>
<43E0C4A7.EE98.0025.0@wicourts.gov>
<4359.1138826175@sss.pgh.pa.us>
<43E0C8F5.EE98.0025.0@wicourts.gov>
>>> On Wed, Feb 1, 2006 at 2:43 pm, in message
<43E0C8F5.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, tgl@sss.pgh.pa.us
Subject: Re: Planner reluctant to start from subquery
In-Reply-To: <43E0CACA.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox