X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 50D839DC89F for ; Wed, 1 Feb 2006 16:18:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26131-03 for ; Wed, 1 Feb 2006 16:18:13 -0400 (AST) X-Greylist: delayed 00:05:06.651322 by SQLgrey- Received: from nz.telogis.com (unknown [203.98.10.169]) by postgresql.org (Postfix) with ESMTP id 1F5139DC85F for ; Wed, 1 Feb 2006 16:18:10 -0400 (AST) Received: from [192.168.3.1] ([::ffff:192.168.3.1]) by nz.telogis.com with esmtp; Thu, 02 Feb 2006 09:12:59 +1300 id 00728B95.43E1164C.00004C5E Message-ID: <43E1164B.7020104@telogis.com> Date: Thu, 02 Feb 2006 09:12:59 +1300 From: Ralph Mason User-Agent: Thunderbird 1.4 (Windows/20050908) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Index Usage using IN Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/23 X-Sequence-Number: 17009 Hi, I have 2 tables both have an index on ID (both ID columns are an oid). I want to find only only rows in one and not the other. Select ID from TableA where ID not IN ( Select ID from Table B) This always generates sequential scans. Table A has about 250,000 rows. Table B has about 250,000 Rows. We should get a Scan on Table B and a Index Lookup on Table A. Is there any way to force this? enable_seqscan off doesn't help at all. The Plan is Seq Scan on tablea(cost=100000000.00..23883423070450.96 rows=119414 width=4) Filter: (NOT (subplan))" SubPlan -> Seq Scan on tableb (cost=100000000.00..100004611.17 rows=242617 width=4) Thanks Ralph