Re: Planner reluctant to start from subquery

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Planner reluctant to start from subquery
Дата
Msg-id 43E0CACA.EE98.0025.0@wicourts.gov
обсуждение исходный текст
Ответ на Re: Planner reluctant to start from subquery  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список pgsql-performance
>>> 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)


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Planner reluctant to start from subquery
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Planner reluctant to start from subquery