Hello List,
I have inherited an existing application. It has a table of events and some
events may reference an earlier
event. We have a script that saves the first N number of events for each
device, but we also want to save
any additional event that is referenced by one of the first N events. The
following query was developed
to do this. It seemed to work ok for a while but one time when it was run it
never finished after running
for a day.
So I did an explain and I see it is doing a seq scan in the last sub plan -
there are about 375,000 rows
in myevent - why isn't it using the index instead of doing a seq scan?
create unique index indx1myevents on myevents (event_log_no)
CREATE INDEX
vacuum analyze
VACUUM
explain insert into myevents select * from t_unit_event_log a where exists
(select b.event_log_no from myevents b
where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
and a.event_ref_log_no = b.event_log_no and a.event_log_no not in
(select event_log_no from myevents)
)
Seq Scan on t_unit_event_log a (cost=0.00..25863477934.49 rows=645692
width=145)
Filter: (subplan)
SubPlan
-> Result (cost=20019.39..20027.70 rows=1 width=4)
One-Time Filter: (($1 = 1) AND ($2 IS NOT NULL) AND (NOT (subplan)))
-> Index Scan using indx1myevents on myevents b
(cost=20019.39..20027.70 rows=1 width=4)
Index Cond: ($2 = event_log_no)
SubPlan
-> Materialize (cost=16579.16..22379.12 rows=432196 width=4)
-> Seq Scan on myevents (cost=0.00..14668.96 rows=432196
width=4)
Why wouldn't the planner use the index instead of doing a seq scan?
Any advice would be greatly appreciated.
Thanks,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
