[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [PERFORM] [HACKERS] temporary indexes
From: "Kevin Grittner" <Kevin.Grittner () wicourts ! gov>
Date: 2006-02-28 21:15:31
Message-ID: 44046913.EE98.0025.0 () wicourts ! gov
[Download RAW message or body]
>>> On Tue, Feb 28, 2006 at 11:05 am, in message
<16076.1141146348@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The limiting factor is that EXISTS subplans
> aren't flattened ... and once that's fixed, I doubt the example
would
> need any new kind of join support.
I rewrote the query to use IN predicates rather than EXISTS predicates,
and the cost estimates look like this:
EXISTS, no index: 1.6 billion
EXISTS, with index: 0.023 billion
IN, no index: 13.7 billion
IN, with index: 10.6 billion
At least for the two EXISTS cases, the estimates were roughly accurate.
These plans were run against the data after the fix, but analyze has
not been run since then, so the estimates should be comparable with the
earlier post.
I'm not used to using the IN construct this way, so maybe someone can
spot something horribly stupid in how I tried to use it.
-Kevin
["datafix-in-plan1.txt" (application/octet-stream)]
Merge Join (cost=13718290070.98..23823398394.05 rows=11456 width=182)
Merge Cond: (("outer"."?column17?" = "inner"."?column8?") AND \
("outer"."?column18?" = "inner"."?column9?")) Join Filter: \
((("inner"."startDate")::date < ("outer"."startDate")::date) AND (NOT \
(subplan)))
-> Sort (cost=13717984139.80..13717984200.52 rows=24289 width=162)
Sort Key: ("Action"."jurorId")::integer, ("Action"."juryYear")::bpchar
-> Nested Loop (cost=3.76..13717982370.58 rows=24289 width=162)
-> HashAggregate (cost=3.76..3.77 rows=1 width=10)
-> Index Scan using "JuryControl_pkey" on "JuryControl" jc \
(cost=0.00..3.75 rows=1 width=10) Index Cond: (("countyNo")::smallint = 40)
-> Index Scan using "Action_pkey" on "Action" \
(cost=0.00..13717982074.61 rows=23377 width=154)
Index Cond: ((("Action"."countyNo")::smallint = 40) AND \
(("Action"."juryYear")::bpchar = ("outer"."juryYear")::bpchar)) Filter: \
((("actionCode")::bpchar <> 'APOOL'::bpchar) AND (("actionCode")::bpchar <> \
'RPOOL'::bpchar) AND (("actionCode")::bpchar <> 'APAN'::bpchar) AND \
(("actionCode")::bpchar <> 'RPAN'::bpchar) AND (("actionCode")::bpchar <> \
'MPOOL'::bpchar) AND (("actionCode")::bpchar <> 'MPAN'::bpchar) AND \
(("actionCode")::bpchar <> 'PR'::bpchar) AND (("actionCode")::bpchar <> \
'JPP'::bpchar) AND (("actionCode")::bpchar <> 'MFROM'::bpchar) AND \
(("actionCode")::bpchar <> 'MNEXT'::bpchar) AND ((("poolId" IS NULL) AND (subplan)) \
OR (("panelId" IS NULL) AND (subplan)))) SubPlan
-> Bitmap Heap Scan on "Action" a1 (cost=6591.32..212610.04 \
rows=51213 width=14)
Recheck Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
Filter: ((("actionCode")::bpchar = 'APAN'::bpchar) OR \
(("actionCode")::bpchar = 'MPAN'::bpchar))
-> Bitmap Index Scan on "Action_pkey" \
(cost=0.00..6591.32 rows=625386 width=0)
Index Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
-> Bitmap Heap Scan on "Action" a1 (cost=6591.32..212610.04 \
rows=67335 width=14)
Recheck Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
Filter: ((("actionCode")::bpchar = 'APOOL'::bpchar) OR \
(("actionCode")::bpchar = 'MPOOL'::bpchar))
-> Bitmap Index Scan on "Action_pkey" \
(cost=0.00..6591.32 rows=625386 width=0)
Index Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
-> Sort (cost=305931.18..306929.78 rows=399440 width=50)
Sort Key: (a2."jurorId")::integer, (a2."juryYear")::bpchar
-> Bitmap Heap Scan on "Action" a2 (cost=15080.55..260185.90 rows=399440 \
width=50) Recheck Cond: (("countyNo")::smallint = 40)
Filter: ((("actionCode")::bpchar = 'APOOL'::bpchar) OR \
(("actionCode")::bpchar = 'RPOOL'::bpchar) OR (("actionCode")::bpchar = \
'APAN'::bpchar) OR (("actionCode")::bpchar = 'RPAN'::bpchar) OR \
(("actionCode")::bpchar = 'MPOOL'::bpchar) OR (("actionCode")::bpchar = \
'MPAN'::bpchar))
-> Bitmap Index Scan on "Action_pkey" (cost=0.00..15080.55 \
rows=1876158 width=0) Index Cond: (("countyNo")::smallint = 40)
SubPlan
-> Bitmap Heap Scan on "Action" a3 (cost=15080.55..278947.48 rows=1110 \
width=14) Recheck Cond: (("countyNo")::smallint = 40)
Filter: (((("actionCode")::bpchar = 'APOOL'::bpchar) OR \
(("actionCode")::bpchar = 'RPOOL'::bpchar) OR (("actionCode")::bpchar = \
'APAN'::bpchar) OR (("actionCode")::bpchar = 'RPAN'::bpchar) OR \
(("actionCode")::bpchar = 'MPOOL'::bpchar) OR (("actionCode")::bpchar = \
'MPAN'::bpchar)) AND (("startDate")::date < ($7)::date) AND (("startDate")::date >= \
($8)::date) AND ((("startDate")::date > ($8)::date) OR (("actionSeqNo")::integer > \
($9)::integer)))
-> Bitmap Index Scan on "Action_pkey" (cost=0.00..15080.55 rows=1876158 \
width=0) Index Cond: (("countyNo")::smallint = 40)
(36 rows)
["datafix-in-plan2.txt" (application/octet-stream)]
Nested Loop (cost=3.74..10625759612.26 rows=8552 width=154)
Join Filter: ((("inner"."startDate")::date < ("outer"."startDate")::date) AND (NOT \
(subplan)))
-> Nested Loop (cost=3.74..7745457469.59 rows=19212 width=134)
-> HashAggregate (cost=3.74..3.75 rows=1 width=10)
-> Index Scan using "JuryControl_pkey" on "JuryControl" jc \
(cost=0.00..3.73 rows=1 width=10) Index Cond: (("countyNo")::smallint = 40)
-> Index Scan using "Action_juror" on "Action" (cost=0.00..7745457179.00 \
rows=22948 width=126)
Index Cond: ((("Action"."countyNo")::smallint = 40) AND \
(("Action"."juryYear")::bpchar = ("outer"."juryYear")::bpchar)) Filter: \
((("actionCode")::bpchar <> 'APOOL'::bpchar) AND (("actionCode")::bpchar <> \
'RPOOL'::bpchar) AND (("actionCode")::bpchar <> 'APAN'::bpchar) AND \
(("actionCode")::bpchar <> 'RPAN'::bpchar) AND (("actionCode")::bpchar <> \
'MPOOL'::bpchar) AND (("actionCode")::bpchar <> 'MPAN'::bpchar) AND \
(("actionCode")::bpchar <> 'PR'::bpchar) AND (("actionCode")::bpchar <> \
'JPP'::bpchar) AND (("actionCode")::bpchar <> 'MFROM'::bpchar) AND \
(("actionCode")::bpchar <> 'MNEXT'::bpchar) AND ((("poolId" IS NULL) AND (subplan)) \
OR (("panelId" IS NULL) AND (subplan)))) SubPlan
-> Bitmap Heap Scan on "Action" a1 (cost=61464.26..66040.00 \
rows=56202 width=14) Recheck Cond: ((((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'APAN'::bpchar)) OR ((("countyNo")::smallint = 40) AND (("actionSeqNo")::integer < \
($0)::integer) AND (("actionCode")::bpchar = 'MPAN'::bpchar))) AND \
(("countyNo")::smallint = 40) AND (("actionSeqNo")::integer < \
($0)::integer))
-> BitmapAnd (cost=61464.26..61464.26 rows=2412 width=0)
-> BitmapOr (cost=54868.69..54868.69 rows=56690 \
width=0)
-> Bitmap Index Scan on "Action_juror" \
(cost=0.00..27434.34 rows=50656 width=0) Index Cond: ((("countyNo")::smallint = 40) \
AND (("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'APAN'::bpchar))
-> Bitmap Index Scan on "Action_juror" \
(cost=0.00..27434.34 rows=6034 width=0) Index Cond: ((("countyNo")::smallint = 40) \
AND (("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'MPAN'::bpchar))
-> Bitmap Index Scan on "Action_pkey" \
(cost=0.00..6595.32 rows=625386 width=0)
Index Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
-> Bitmap Heap Scan on "Action" a1 (cost=61464.26..66687.17 \
rows=64460 width=14) Recheck Cond: ((((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'APOOL'::bpchar)) OR ((("countyNo")::smallint = 40) AND (("actionSeqNo")::integer < \
($0)::integer) AND (("actionCode")::bpchar = 'MPOOL'::bpchar))) AND \
(("countyNo")::smallint = 40) AND (("actionSeqNo")::integer < \
($0)::integer))
-> BitmapAnd (cost=61464.26..61464.26 rows=2767 width=0)
-> BitmapOr (cost=54868.69..54868.69 rows=65029 \
width=0)
-> Bitmap Index Scan on "Action_juror" \
(cost=0.00..27434.34 rows=58995 width=0) Index Cond: ((("countyNo")::smallint = 40) \
AND (("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'APOOL'::bpchar))
-> Bitmap Index Scan on "Action_juror" \
(cost=0.00..27434.34 rows=6034 width=0) Index Cond: ((("countyNo")::smallint = 40) \
AND (("actionSeqNo")::integer < ($0)::integer) AND (("actionCode")::bpchar = \
'MPOOL'::bpchar))
-> Bitmap Index Scan on "Action_pkey" \
(cost=0.00..6595.32 rows=625386 width=0)
Index Cond: ((("countyNo")::smallint = 40) AND \
(("actionSeqNo")::integer < ($0)::integer))
-> Index Scan using "Action_juror" on "Action" a2 (cost=0.00..10.22 rows=1 \
width=50) Index Cond: (((a2."countyNo")::smallint = 40) AND ((a2."juryYear")::bpchar \
= ("outer"."juryYear")::bpchar) AND ((a2."jurorId")::integer = \
("outer"."jurorId")::integer)) Filter: ((("actionCode")::bpchar = 'APOOL'::bpchar) \
OR (("actionCode")::bpchar = 'RPOOL'::bpchar) OR (("actionCode")::bpchar = \
'APAN'::bpchar) OR (("actionCode")::bpchar = 'RPAN'::bpchar) OR \
(("actionCode")::bpchar = 'MPOOL'::bpchar) OR (("actionCode")::bpchar = \
'MPAN'::bpchar)) SubPlan
-> Index Scan using "Action_pkey" on "Action" a3 (cost=0.00..299820.98 \
rows=1039 width=14) Index Cond: (("countyNo")::smallint = 40)
Filter: (((("actionCode")::bpchar = 'APOOL'::bpchar) OR \
(("actionCode")::bpchar = 'RPOOL'::bpchar) OR (("actionCode")::bpchar = \
'APAN'::bpchar) OR (("actionCode")::bpchar = 'RPAN'::bpchar) OR \
(("actionCode")::bpchar = 'MPOOL'::bpchar) OR (("actionCode")::bpchar = \
'MPAN'::bpchar)) AND (("startDate")::date < ($7)::date) AND (("startDate")::date >= \
($8)::date) AND ((("startDate")::date > ($8)::date) OR (("actionSeqNo")::integer > \
($9)::integer))) (37 rows)
["datafix-in.qry" (application/octet-stream)]
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic