X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B27B4D1E8CB for ; Tue, 20 Apr 2004 21:41:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 87736-05 for ; Tue, 20 Apr 2004 21:41:37 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id E12FFD1EEBE for ; Tue, 20 Apr 2004 14:52:33 -0300 (ADT) Received: (qmail 11338 invoked by uid 500); 20 Apr 2004 17:56:32 -0000 Date: Tue, 20 Apr 2004 12:56:32 -0500 From: Bruno Wolff III To: Chris Hoover Cc: pgsql-performance@postgresql.org Subject: Re: Use of subquery causes seq scan??? Message-ID: <20040420175632.GB11203@wolff.to> Mail-Followup-To: Chris Hoover , pgsql-performance@postgresql.org References: <200404151248.16067.jeremy.guthrie@berbee.com> <200404201020.05699.revoohc@sermonaudio.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200404201020.05699.revoohc@sermonaudio.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/316 X-Sequence-Number: 6616 Please don't reply to messages to start new threads. On Tue, Apr 20, 2004 at 10:20:05 -0400, Chris Hoover wrote: > I need some help. I have a query that refuses to use the provided index and > is always sequentially scanning causing me large performance headaches. Here > is the basic situation: > > Table A: > inv_num int > type char > . > . > . > pkey (inv_num, type) > indx(inv_num) > > Table B (has the same primary key) > > Select * > from table a > where inv_num in (select inv_num from table b where ....) > > Doing this causes sequential scans of both tables. If I do a set > enable_seqscan to false before the query, I get an index scan of table b but > still seq scan table a. > > Is there anyway to force table a to use this index (or another) and not > sequentially scan the table? > > I'm running 7.3.4 on RedHat EL 2.1. IN was slow in 7.3.x and before. The query will probably run much better as is in 7.4 and above. In 7.3 you want to rewrite it as a join or using EXISTS.