8.3.1 query plan - Mailing list pgsql-general

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