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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Jun 16, 2009 at 5:30 PM, Robert Haas<robertmhaas@xxxxxxxxx> wrote:
> On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner<synfinatic@xxxxxxxxx> wrote:
>> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
>> Herrera<alvherre@xxxxxxxxxxxxxxxxx> 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

That's a syntax error on 8.3.3... I don't see anywhere in the docs
where the delete command allows for multiple FROM statements.  Perhaps
you meant:

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

Is that right?

> 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.

Wow, glad I asked... I never would of figured that out.

-- 
Aaron Turner
http://synfin.net/
http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Windows
Those who would give up essential Liberty, to purchase a little temporary
Safety, deserve neither Liberty nor Safety.
    -- Benjamin Franklin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux