Re: High cost of ... where ... not in (select ...)

On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner<synfinatic@gmail.com> wrote:
> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
> Herrera<alvherre@commandprompt.com> wrote:
>> Aaron Turner escribió:
>>> I'm trying to figure out how to optimize this query (yes, I ran vacuum/analyze):
>>>
>>> musecurity=# explain DELETE FROM muapp.pcap_store WHERE pcap_storeid
>>> NOT IN (SELECT pcap_storeid FROM muapp.pcap_store_log);
>>
>> What PG version is this?
>
> Doh, just realized I didn't reply back to list.   It's version 8.3.3.
>
> Also, pcap_storeid is unique in pcap_store_log

Speaking as one who has dealt with this frustration more than once,
you can typically get better performance with something like:

DELETE FROM muapp.pcap_store AS x
FROM muapp.pcap_store a
LEFT JOIN muapp.pcap_store_log b ON a.pcap_store_id = b.pcap_storeid
WHERE x.pcap_storeid = a.pcap_storeid AND b.pcap_storeid IS NULL

This is emphatically lame, but there you have it.  It's first of all
lame that we can't do a better job optimizing NOT-IN, at least when
the expression within the subselect is known to be not-null, and it's
secondly lame that the syntax of DELETE doesn't permit a LEFT JOIN
without a self-JOIN.

</rant>

...Robert