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