Tom Lane wrote:
That's very strange --- the estimated cost of the seqscan is high enough
that the planner should have chosen a nestloop with inner indexscan on
the big table. I'm not sure about the join-order point, but the hash
plan for the first join seems wrong in any case.
Um, you do have an index on eventactivity.incidentid, right? What's the
datatype(s) of the incidentid columns? What happens to the plan if you
turn off enable_hashjoin and enable_mergejoin?
regards, tom lane
Yes, eventactivity.incidentid is indexed. The datatype is varchar(40).
Although, by checking this, I noticed that k_h.incidentid was
varchar(100). Perhaps the difference in length between the keys caused
the planner to not use the fastest method? I have no defense as to why
those aren't the same.. I will make them so and check.
Here's the EXPLAIN analyze with enable_hashjoin = off and
enable_mergejoin = off :
Limit (cost=4226535.73..4226544.46 rows=698 width=82) (actual
time=74339.016..74356.521 rows=888 loops=1)
-> Unique (cost=4226535.73..4226544.46 rows=698 width=82) (actual
time=74339.011..74354.073 rows=888 loops=1)
-> Sort (cost=4226535.73..4226537.48 rows=698 width=82)
(actual time=74339.003..74344.031 rows=3599 loops=1)
Sort Key: eventmain.entrydate, eventmain.incidentid,
eventgeo.eventlocation, eventactivity.recordtext
-> Nested Loop (cost=0.00..4226502.76 rows=698
width=82) (actual time=921.325..74314.959 rows=3599 loops=1)
-> Nested Loop (cost=0.00..4935.61 rows=731
width=72) (actual time=166.354..14638.308 rows=1162 loops=1)
-> Nested Loop (cost=0.00..2482.47 rows=741
width=50) (actual time=150.396..7348.013 rows=1162 loops=1)
-> Index Scan using k_h_id_idx on k_h
(cost=0.00..217.55 rows=741 width=14) (actual time=129.540..1022.243
rows=1162 loops=1)
Index Cond: (id = 33396)
Filter: ((entrydate >=
'2006-01-01 00:00:00'::timestamp without time zone) AND (entrydate <
'2006-04-08 00:00:00'::timestamp without time zone))
-> Index Scan using
eventgeo_incidentid_idx on eventgeo (cost=0.00..3.04 rows=1 width=36)
(actual time=5.260..5.429 rows=1 loops=1162)
Index Cond:
((eventgeo.incidentid)::text = ("outer".incidentid)::text)
-> Index Scan using eventmain_incidentid_idx
on eventmain (cost=0.00..3.30 rows=1 width=22) (actual
time=5.976..6.259 rows=1 loops=1162)
Index Cond:
((eventmain.incidentid)::text = ("outer".incidentid)::text)
-> Index Scan using eventactivity1 on
eventactivity (cost=0.00..5774.81 rows=20 width=52) (actual
time=29.768..51.334 rows=3 loops=1162)
Index Cond: (("outer".incidentid)::text =
(eventactivity.incidentid)::text)
Filter: ((((' '::text || (recordtext)::text)
|| ' '::text) ~~ '%HAL%'::text) AND (entrydate >= '2006-01-01
00:00:00'::timestamp without time zone) AND (entrydate < '2006-04-08
00:00:00'::timestamp without time zone))
