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

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

 



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);
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on pcap_store  (cost=4008.22..348521303.54 rows=106532 width=6)
   Filter: (NOT (subplan))
   SubPlan
     ->  Materialize  (cost=4008.22..6765.98 rows=205475 width=4)
           ->  Seq Scan on pcap_store_log  (cost=0.00..3099.75
rows=205475 width=4)
(5 rows)

musecurity=# \d muapp.pcap_store
                                               Table "muapp.pcap_store"
      Column       |          Type          |
      Modifiers
-------------------+------------------------+-------------------------------------------------------------------------
 pcap_storeid      | integer                | not null default
nextval('muapp.pcap_store_pcap_storeid_seq'::regclass)
 filename          | character varying(255) |
 test_run_dutid    | integer                | default 0
 userid            | integer                | not null default 0
 analysis_recordid | bigint                 |
 io_xml            | character varying(255) |
Indexes:
    "pcap_store_pkey" PRIMARY KEY, btree (pcap_storeid)
Foreign-key constraints:
    "pcap_store_analysis_recordid_fkey" FOREIGN KEY
(analysis_recordid) REFERENCES muapp.analysis(recordid) ON DELETE
CASCADE
    "pcap_store_test_run_dutid_fkey" FOREIGN KEY (test_run_dutid)
REFERENCES muapp.test_run_dut(test_run_dutid) ON DELETE CASCADE
    "pcap_store_userid_fkey" FOREIGN KEY (userid) REFERENCES
mucore."user"(recordid) ON DELETE CASCADE

As you see, the sequence scan on pcap_store is killing me, even though
there appears to be a perfectly good index.  Is there a better way
construct this query?

Thanks,
Aaron

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