From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Aaron Turner <synfinatic(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: High cost of ... where ... not in (select ...) |
Date: | 2009-06-17 01:36:33 |
Message-ID: | 603c8f070906161836n49a28a30q3440e6462c760a78@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner<synfinatic(at)gmail(dot)com> wrote:
> On Tue, Jun 16, 2009 at 5:30 PM, Robert Haas<robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner<synfinatic(at)gmail(dot)com> wrote:
>>> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
>>> Herrera<alvherre(at)commandprompt(dot)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
>
> 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?
Woops, yes, I think that's it.
(but I don't guarantee that it won't blow up your entire universe, so
test it carefully first)
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Turner | 2009-06-17 01:46:48 | Re: High cost of ... where ... not in (select ...) |
Previous Message | Aaron Turner | 2009-06-17 01:23:45 | Re: High cost of ... where ... not in (select ...) |