Re: 8.3.1 query plan

Martijn van Oosterhout wrote:
> On Wed, Aug 27, 2008 at 11:00:54AM -0400, Steve Clark wrote:
>
>>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
>
>
> Are there NULLs in this subtable? NOT IN must check the entire table
> for NULLs. Try changing the query to use NOT EXISTS.
>
> Have a nice day,

Hi Martijn,

I guess I misunderstand what you are saying because this is what I get now:

srm2=# explain insert into myevents select * from t_unit_event_log a where exists
srm2-#         (select b.event_log_no from myevents b
srm2(#          where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
srm2(#                 and a.event_ref_log_no = b.event_log_no and a.event_log_no not exists
srm2(#                         (select event_log_no from myevents)
srm2(#         );
ERROR:  syntax error at or near "exists"
LINE 4: ...nt_ref_log_no = b.event_log_no and a.event_log_no not exists

Regards,
Steve