X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DEDE09DC85F for ; Wed, 1 Feb 2006 16:51:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31937-02 for ; Wed, 1 Feb 2006 16:51:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 7B4E79DC89F for ; Wed, 1 Feb 2006 16:51:07 -0400 (AST) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 01 Feb 2006 14:51:09 -0600 Message-Id: <43E0CACA.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Wed, 01 Feb 2006 14:50:51 -0600 From: "Kevin Grittner" To: "Tom Lane" , "Kevin Grittner" Cc: Subject: Re: Planner reluctant to start from subquery 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> In-Reply-To: <43E0C8F5.EE98.0025.0@wicourts.gov> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.046 required=5 tests=[AWL=0.046] X-Spam-Score: 0.046 X-Spam-Level: X-Archive-Number: 200602/30 X-Sequence-Number: 17016 >>> On Wed, Feb 1, 2006 at 2:43 pm, in message <43E0C8F5.EE98.0025.0@wicourts.gov>, "Kevin Grittner" 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)