On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner<synfinatic@gmail.com> wrote:
> On Tue, Jun 16, 2009 at 5:30 PM, Robert Haas<robertmhaas@gmail.com> wrote:
>> 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
>
> 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
