High cost of ... where ... not in (select ...) - Mailing list pgsql-performance

From Aaron Turner
Subject High cost of ... where ... not in (select ...)
Date
Msg-id 1ca1c1410906161428la12f7eck839e8c2fbbf984c4@mail.gmail.com
Whole thread Raw
Responses Re: High cost of ... where ... not in (select ...)  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: "Mark Steben"
Date:
Subject: Performance discrepancy
Next
From: Alvaro Herrera
Date:
Subject: Re: High cost of ... where ... not in (select ...)