Обсуждение: Planner reluctant to start from subquery
We're converting from a commercial database product to PostgreSQL, and
generally things are going well. While the licensing agreement with the
commercial vendor prohibits publication of benchmarks without their
written consent, I'll just say that on almost everything, PostgreSQL is
faster.
We do have a few queries where PostgreSQL is several orders of
magnitude slower. It appears that the reason it is choosing a bad plan
is that it is reluctant to start from a subquery when there is an outer
join in the FROM clause. Pasted below are four logically equivalent
queries. The first is a much stripped down version of one of the
production queries. The second turns the EXISTS expression into an IN
expression. (In the full query this makes very little difference; as I
pared down the query, the planner started to do better with the IN form
before the EXISTS form.) The third query is the fastest, but isn't
portable enough for our mixed environment. The fourth is the best
workaround I've found, but I get a bit queasy when I have to use the
DISTINCT modifier on a query.
Any other suggestions?
-Kevin
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
OR ("C"."caseType" = 'PA' AND "C"."isConfidential" = false)
)
AND "C"."countyNo" = 66
AND EXISTS
(
SELECT *
FROM "DocImageMetaData" "D"
WHERE "D"."isEFiling" = true
AND "D"."insertedDate" BETWEEN '2006-01-01' AND '2006-01-07'
AND "D"."countyNo" = 66
AND "D"."countyNo" = "C"."countyNo"
AND "D"."caseNo" = "C"."caseNo"
)
ORDER BY "caseNo"
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=786467.94..786504.40 rows=14584 width=210) (actual
time=7391.295..7391.418 rows=51 loops=1)
Sort Key: "C"."caseNo"
-> Hash Left Join (cost=49.35..785459.30 rows=14584 width=210)
(actual time=6974.819..7390.802 rows=51 loops=1)
Hash Cond: ((("outer"."caseType")::bpchar =
("inner"."caseType")::bpchar) AND (("outer"."countyNo")::smallint =
("inner"."countyNo")::smallint))
Filter: (("inner"."profileName" IS NOT NULL) OR
((("outer"."caseType")::bpchar = 'PA'::bpchar) AND (NOT
"outer"."isConfidential")))
-> Merge Join (cost=0.00..783366.38 rows=14584 width=210)
(actual time=6972.672..7388.329 rows=51 loops=1)
Merge Cond: (("outer"."caseNo")::bpchar =
("inner"."caseNo")::bpchar)
-> Index Scan using "Case_pkey" on "Case" "C"
(cost=0.00..624268.11 rows=65025 width=208) (actual
time=4539.588..4927.730 rows=22 loops=1)
Index Cond: (("countyNo")::smallint = 66)
Filter: (subplan)
SubPlan
-> Index Scan using "DocImageMetaData_pkey" on
"DocImageMetaData" "D" (cost=0.00..3.89 rows=1 width=212) (actual
time=0.012..0.012 rows=0 loops=203171)
Index Cond: ((("countyNo")::smallint = 66)
AND (("countyNo")::smallint = ($0)::smallint) AND (("caseNo")::bpchar =
($1)::bpchar))
Filter: ("isEFiling" AND
(("insertedDate")::date >= '2006-01-01'::date) AND
(("insertedDate")::date <= '2006-01-07'::date))
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..158657.86 rows=191084 width=22) (actual time=0.769..1646.381
rows=354058 loops=1)
Index Cond: (66 = ("countyNo")::smallint)
-> Hash (cost=49.22..49.22 rows=27 width=31) (actual
time=1.919..1.919 rows=28 loops=1)
-> Bitmap Heap Scan on "WccaPermCaseType" "WPCT"
(cost=2.16..49.22 rows=27 width=31) (actual time=0.998..1.782 rows=28
loops=1)
Recheck Cond: ((("countyNo")::smallint = 66) AND
(("profileName")::text = 'PUBLIC'::text))
-> Bitmap Index Scan on "WccaPermCaseType_pkey"
(cost=0.00..2.16 rows=27 width=0) (actual time=0.684..0.684 rows=28
loops=1)
Index Cond: ((("countyNo")::smallint = 66)
AND (("profileName")::text = 'PUBLIC'::text))
Total runtime: 7392.577 ms
(22 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 ( "WPCT"."profileName" IS NOT NULL
OR ("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=284708.49..284708.50 rows=1 width=210) (actual
time=8962.995..8963.103 rows=51 loops=1)
Sort Key: "C"."caseNo"
-> Hash Join (cost=2359.31..284708.48 rows=1 width=210) (actual
time=8401.856..8962.606 rows=51 loops=1)
Hash Cond: (("outer"."caseNo")::bpchar =
("inner"."caseNo")::bpchar)
-> Hash Left Join (cost=49.35..282252.68 rows=29167
width=228) (actual time=32.120..8184.880 rows=312718 loops=1)
Hash Cond: ((("outer"."caseType")::bpchar =
("inner"."caseType")::bpchar) AND (("outer"."countyNo")::smallint =
("inner"."countyNo")::smallint))
Filter: (("inner"."profileName" IS NOT NULL) OR
((("outer"."caseType")::bpchar = 'PA'::bpchar) AND (NOT
"outer"."isConfidential")))
-> Merge Join (cost=0.00..278116.34 rows=29167
width=228) (actual time=0.596..6236.238 rows=362819 loops=1)
Merge Cond: (("outer"."caseNo")::bpchar =
("inner"."caseNo")::bpchar)
-> Index Scan using "Case_pkey" on "Case" "C"
(cost=0.00..118429.72 rows=130049 width=208) (actual
time=0.265..1303.409 rows=203171 loops=1)
Index Cond: (("countyNo")::smallint = 66)
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..158657.86 rows=191084 width=22) (actual time=0.303..2310.735
rows=362819 loops=1)
Index Cond: (66 = ("countyNo")::smallint)
-> Hash (cost=49.22..49.22 rows=27 width=31) (actual
time=31.406..31.406 rows=28 loops=1)
-> Bitmap Heap Scan on "WccaPermCaseType" "WPCT"
(cost=2.16..49.22 rows=27 width=31) (actual time=23.498..31.284 rows=28
loops=1)
Recheck Cond: ((("countyNo")::smallint = 66)
AND (("profileName")::text = 'PUBLIC'::text))
-> Bitmap Index Scan on
"WccaPermCaseType_pkey" (cost=0.00..2.16 rows=27 width=0) (actual
time=17.066..17.066 rows=28 loops=1)
Index Cond: ((("countyNo")::smallint =
66) AND (("profileName")::text = 'PUBLIC'::text))
-> Hash (cost=2309.95..2309.95 rows=1 width=18) (actual
time=24.255..24.255 rows=22 loops=1)
-> HashAggregate (cost=2309.94..2309.95 rows=1
width=18) (actual time=24.132..24.185 rows=22 loops=1)
-> Index Scan using
"DocImageMetaData_CountyNoInsertedDate" on "DocImageMetaData" "D"
(cost=0.00..2309.93 rows=6 width=18) (actual time=7.362..23.933 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"
Total runtime: 8964.044 ms
(24 rows)
explain analyze
SELECT "C".*, "P"."partyNo"
FROM "Case" "C"
JOIN "Party" "P" ON ("C"."caseNo" = "P"."caseNo" AND "C"."countyNo" =
"P"."countyNo")
JOIN
(
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
GROUP BY "D"."caseNo"
) "DD"
ON ("DD"."caseNo" = "C"."caseNo")
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
OR ("C"."caseType" = 'PA' AND "C"."isConfidential" = false)
)
AND "C"."countyNo" = 66
ORDER BY
"caseNo"
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2321.49..2321.50 rows=1 width=210) (actual
time=7.753..7.859 rows=51 loops=1)
Sort Key: "C"."caseNo"
-> Nested Loop Left Join (cost=2309.94..2321.48 rows=1 width=210)
(actual time=3.982..7.369 rows=51 loops=1)
Join Filter: (("outer"."countyNo")::smallint =
("inner"."countyNo")::smallint)
Filter: (("inner"."profileName" IS NOT NULL) OR
((("outer"."caseType")::bpchar = 'PA'::bpchar) AND (NOT
"outer"."isConfidential")))
-> Nested Loop (cost=2309.94..2317.99 rows=1 width=210)
(actual time=3.906..5.717 rows=51 loops=1)
-> Nested Loop (cost=2309.94..2313.51 rows=1
width=240) (actual time=3.847..4.660 rows=22 loops=1)
-> HashAggregate (cost=2309.94..2309.95 rows=1
width=18) (actual time=3.775..3.830 rows=22 loops=1)
-> Index Scan using
"DocImageMetaData_CountyNoInsertedDate" on "DocImageMetaData" "D"
(cost=0.00..2309.93 rows=6 width=18) (actual time=0.732..3.601 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.020..0.022 rows=1
loops=22)
Index Cond: ((("C"."countyNo")::smallint =
66) AND (("outer"."caseNo")::bpchar = ("C"."caseNo")::bpchar))
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..4.46 rows=2 width=22) (actual time=0.019..0.028 rows=2
loops=22)
Index Cond: ((66 = ("P"."countyNo")::smallint) AND
(("outer"."caseNo")::bpchar = ("P"."caseNo")::bpchar))
-> Index Scan using "WccaPermCaseType_ProfileName" on
"WccaPermCaseType" "WPCT" (cost=0.00..3.47 rows=1 width=31) (actual
time=0.015..0.018 rows=1 loops=51)
Index Cond: ((("WPCT"."profileName")::text =
'PUBLIC'::text) AND (("outer"."caseType")::bpchar =
("WPCT"."caseType")::bpchar) AND (("WPCT"."countyNo")::smallint = 66))
Total runtime: 8.592 ms
(18 rows)
explain analyze
SELECT DISTINCT "C".*, "P"."partyNo"
FROM "Case" "C"
JOIN "Party" "P" ON ("P"."countyNo" = "C"."countyNo" AND "P"."caseNo"
= "C"."caseNo")
JOIN "DocImageMetaData" "D" ON ("D"."countyNo" = "C"."countyNo" AND
"D"."caseNo" = "C"."caseNo")
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
OR ("C"."caseType" = 'PA' AND "C"."isConfidential" = false)
)
AND "C"."countyNo" = 66
AND "D"."countyNo" = 66
AND "D"."isEFiling" = true
AND "D"."insertedDate" BETWEEN '2006-01-01' AND '2006-01-07'
ORDER BY
"caseNo"
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=2339.19..2339.28 rows=1 width=210) (actual
time=9.539..10.044 rows=51 loops=1)
-> Sort (cost=2339.19..2339.19 rows=1 width=210) (actual
time=9.532..9.678 rows=68 loops=1)
Sort Key: "C"."caseNo", "C"."countyNo", "C"."caseType",
"C"."filingDate", "C"."isConfidential", "C"."isDomesticViolence",
"C"."isFiledWoCtofc", "C"."lastChargeSeqNo", "C"."lastCvJgSeqNo",
"C"."lastHistSeqNo", "C"."lastPartySeqNo", "C"."lastRelSeqNo",
"C"."statusCode", "C"."bondId", "C"."branchId", "C".caption,
"C"."daCaseNo", "C"."dispCtofcNo", "C"."fileCtofcDate",
"C"."filingCtofcNo", "C"."issAgencyNo", "C"."maintCode",
"C"."oldCaseNo", "C"."plntfAgencyNo", "C"."previousRespCo",
"C"."prosAgencyNo", "C"."prosAtty", "C"."respCtofcNo",
"C"."wcisClsCode", "C"."isSeal", "C"."isExpunge",
"C"."isElectronicFiling", "C"."isPartySeal", "P"."partyNo"
-> Nested Loop Left Join (cost=0.00..2339.18 rows=1
width=210) (actual time=0.857..7.901 rows=68 loops=1)
Join Filter: (("outer"."countyNo")::smallint =
("inner"."countyNo")::smallint)
Filter: (("inner"."profileName" IS NOT NULL) OR
((("outer"."caseType")::bpchar = 'PA'::bpchar) AND (NOT
"outer"."isConfidential")))
-> Nested Loop (cost=0.00..2335.68 rows=1 width=210)
(actual time=0.786..5.784 rows=68 loops=1)
-> Nested Loop (cost=0.00..2331.20 rows=1
width=226) (actual time=0.728..4.313 rows=29 loops=1)
-> Index Scan using
"DocImageMetaData_CountyNoInsertedDate" on "DocImageMetaData" "D"
(cost=0.00..2309.93 rows=6 width=20) (actual time=0.661..3.266 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.021 rows=1
loops=29)
Index Cond:
((("C"."countyNo")::smallint = 66) AND (("outer"."caseNo")::bpchar =
("C"."caseNo")::bpchar))
-> Index Scan using "Party_pkey" on "Party" "P"
(cost=0.00..4.46 rows=2 width=22) (actual time=0.018..0.027 rows=2
loops=29)
Index Cond: ((66 =
("P"."countyNo")::smallint) AND (("P"."caseNo")::bpchar =
("outer"."caseNo")::bpchar))
-> Index Scan using "WccaPermCaseType_ProfileName" on
"WccaPermCaseType" "WPCT" (cost=0.00..3.47 rows=1 width=31) (actual
time=0.014..0.017 rows=1 loops=68)
Index Cond: ((("WPCT"."profileName")::text =
'PUBLIC'::text) AND (("outer"."caseType")::bpchar =
("WPCT"."caseType")::bpchar) AND (("WPCT"."countyNo")::smallint = 66))
Total runtime: 10.748 ms
(18 rows)
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> We do have a few queries where PostgreSQL is several orders of
> magnitude slower. It appears that the reason it is choosing a bad plan
> is that it is reluctant to start from a subquery when there is an outer
> join in the FROM clause.
AFAICT this case doesn't really hinge on the outer join at all. The
problem is that EXISTS subqueries aren't well optimized. I would have
expected an equivalent IN clause to work better. In fact, I'm not
clear why the planner isn't finding the cheapest plan (which it does
estimate as cheapest) from the IN version you posted. What PG version
is this exactly?
> ... The third query is the fastest, but isn't
> portable enough for our mixed environment.
Not really relevant to the problem, but what's wrong with it? Looks
like standard SQL to me.
regards, tom lane
>>> On Wed, Feb 1, 2006 at 1:34 pm, in message <3759.1138822464@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> We do have a few queries where PostgreSQL is several orders of >> magnitude slower. It appears that the reason it is choosing a bad plan >> is that it is reluctant to start from a subquery when there is an outer >> join in the FROM clause. > > AFAICT this case doesn't really hinge on the outer join at all. The > problem is that EXISTS subqueries aren't well optimized. I would have > expected an equivalent IN clause to work better. In fact, I'm not > clear why the planner isn't finding the cheapest plan (which it does > estimate as cheapest) from the IN version you posted. All I know is that trying various permutations, I saw it pick a good plan for the IN format when I eliminated the last outer join in the FROM clause. I know it isn't conclusive, but it was a correlation which suggested a possible causality to me. The EXISTS never chose a reasonable plan on this one, although we haven't had a problem with them in most cases. > What PG version is this exactly? select version() reports: PostgreSQL 8.1.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) However, this was actually built off the 8.1 stable branch as of Jan. 13th at about 3 p.m. This build does contain the implementation of standard_conforming_strings for which I recently posted a patch. The make was configured with: --enable-integer-datetimes --enable-debug --disable-nls > >> ... The third query is the fastest, but isn't >> portable enough for our mixed environment. > > Not really relevant to the problem, but what's wrong with it? Looks > like standard SQL to me. It is absolutely compliant with the standards. Unfortunately, we are under a "lowest common denominator" portability mandate. I notice that support for this syntax has improved since we last set our limits; I'll try to get this added to our allowed techniques. I can't complain about the portability mandate -- without it, we would undoubtedly have had product specific code for the commercial product which would have made migration to PostgreSQL much more painful. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... expected an equivalent IN clause to work better. In fact, I'm not
>> clear why the planner isn't finding the cheapest plan (which it does
>> estimate as cheapest) from the IN version you posted.
> All I know is that trying various permutations, I saw it pick a good
> plan for the IN format when I eliminated the last outer join in the FROM
> clause. I know it isn't conclusive, but it was a correlation which
> suggested a possible causality to me.
But there is still an outer join in your third example (the one with the
best plan), so that doesn't seem to hold water. In any case, the way
that IN planning works these days it really should have considered the
plan equivalent to your JOIN-against-GROUP-BY variant.
I'm interested to poke at this ... are you in a position to provide a
test case?
regards, tom lane
>>> On Wed, Feb 1, 2006 at 2:14 pm, in message <4218.1138824885@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> ... expected an equivalent IN clause to work better. In fact, I'm not >>> clear why the planner isn't finding the cheapest plan (which it does >>> estimate as cheapest) from the IN version you posted. > >> All I know is that trying various permutations, I saw it pick a good >> plan for the IN format when I eliminated the last outer join in the FROM >> clause. I know it isn't conclusive, but it was a correlation which >> suggested a possible causality to me. > > But there is still an outer join in your third example (the one with the > best plan), so that doesn't seem to hold water. Right, if I moved the DocImageMetaData from a subquery in the WHERE clause up to the FROM clause, or I eliminated all OUTER JOINs, it chose a good plan. Of course, this was just playing with a few dozen permutations, so it proves nothing -- I'm just sayin'.... > In any case, the way > that IN planning works these days it really should have considered the > plan equivalent to your JOIN- against- GROUP- BY variant. > > I'm interested to poke at this ... are you in a position to provide a > test case? I can't supply the original data, since many of the tables have millions of rows, with some of the data (related to juvenile, paternity, sealed, and expunged cases) protected by law. I could try to put together a self-contained example, but I'm not sure the best way to do that, since the table sizes and value distributions may be significant here. Any thoughts on that? -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm interested to poke at this ... are you in a position to provide a
>> test case?
> I can't supply the original data, since many of the tables have
> millions of rows, with some of the data (related to juvenile, paternity,
> sealed, and expunged cases) protected by law. I could try to put
> together a self-contained example, but I'm not sure the best way to do
> that, since the table sizes and value distributions may be significant
> here. Any thoughts on that?
I think that the only aspect of the data that really matters here is the
number of distinct values, which would affect decisions about whether
HashAggregate is appropriate or not. And you could probably get the
same thing to happen with at most a few tens of thousands of rows.
Also, all we need to worry about is the columns used in the WHERE/JOIN
conditions, which looks to be mostly case numbers, dates, and county
identification ... how much confidential info is there in that? At
worst you could translate the case numbers to some randomly generated
identifiers.
regards, tom lane
>>> On Wed, Feb 1, 2006 at 2:36 pm, in message <4359.1138826175@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I'm interested to poke at this ... are you in a position to provide a >>> test case? > >> I can't supply the original data, since many of the tables have >> millions of rows, with some of the data (related to juvenile, paternity, >> sealed, and expunged cases) protected by law. I could try to put >> together a self- contained example, but I'm not sure the best way to do >> that, since the table sizes and value distributions may be significant >> here. Any thoughts on that? > > I think that the only aspect of the data that really matters here is the > number of distinct values, which would affect decisions about whether > HashAggregate is appropriate or not. And you could probably get the > same thing to happen with at most a few tens of thousands of rows. > > Also, all we need to worry about is the columns used in the WHERE/JOIN > conditions, which looks to be mostly case numbers, dates, and county > identification ... how much confidential info is there in that? At > worst you could translate the case numbers to some randomly generated > identifiers. OK, I could probably obliterate name, addresses, etc. in a copy of the data (those aren't significant to the query anyway) and provide a test case. However, I just found another clue. Since you were so confident it couldn't be the outer join, I went looking for what else I changed at the same time. I eliminated the code referencing that table, which contained an OR. I've seen ORs cause nasty problems with optimizers in the past. I took out the OR in the where clause, without eliminating that last outer join, and it optimized fine. I'll hold off a bit to see if you still need the test case. ;-) -Kevin
>>> 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)
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Since you were so confident it couldn't be the outer join, I went
> looking for what else I changed at the same time. I eliminated the code
> referencing that table, which contained an OR. I've seen ORs cause
> nasty problems with optimizers in the past. I took out the OR in the
> where clause, without eliminating that last outer join, and it optimized
> fine.
I don't think that OR is relevant either, since again it's present in
both the well-optimized and badly-optimized variants that you posted.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes [offlist]:
> Attached is a pg_dump -c file with only the required rows (none of
> which contain confidential data), and 0.1% of the rows from the larger
> tables. It does show the same pattern of costing and plan choice.
Thanks for the test case. The first thing I found out was that HEAD
does generate the fast plan from the IN case, while 8.1 does not, and
after a bit of digging the reason became clear. The initial state
that the planner starts from is essentially
SELECT ... FROM ((C JOIN P) LEFT JOIN WPCT) IN-JOIN D
(IN-JOIN being a notation for the way the planner thinks about IN, which
is that it's a join with some special runtime behavior). The problem
with this is that outer joins don't always commute with other joins,
and up through 8.1 we didn't have any code to analyze whether or not
re-ordering outer joins is safe. So we never did it at all. HEAD does
have such code, and so it is able to re-order the joins enough to
generate the fast plan, which is essentially
SELECT ... FROM ((C IN-JOIN D) JOIN P) LEFT JOIN WPCT
This is why eliminating the OUTER JOIN improved things for you. Your
manual rearrangement into a JOIN-with-GROUP-BY inside the OUTER JOIN
essentially duplicates the IN-JOIN rearrangement that HEAD is able to
do for itself.
BTW, the reason why getting rid of the OR improved matters is that:
(a) with the "WPCT"."profileName" IS NOT NULL part as a top-level WHERE
clause, the planner could prove that it could reduce the OUTER JOIN to
a JOIN (because no null-extended row would pass that qual), whereupon
it had join order flexibility again.
(b) with the "C"."caseType" = 'PA' AND "C"."isConfidential" = false
part as a top-level WHERE clause, there still wasn't any join order
flexibility, but this added restriction on C reduced the number of C
rows enough that there wasn't a performance problem anyway.
So it's all fairly clear now what is happening. The good news is we
have this fixed for 8.2, the bad news is that that patch is much too
large to consider back-patching.
regards, tom lane