Skip site navigation (1) Skip section navigation (2)

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

From: Aaron Turner <synfinatic(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: High cost of ... where ... not in (select ...)
Date: 2009-06-16 21:28:19
Message-ID: 1ca1c1410906161428la12f7eck839e8c2fbbf984c4@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-06-16 21:37:50
Subject: Re: High cost of ... where ... not in (select ...)
Previous:From: Mark StebenDate: 2009-06-16 21:01:35
Subject: Performance discrepancy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group