On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner<synfinatic@xxxxxxxxx> wrote: > 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? 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
