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 D8E05D1D08B for ; Tue, 20 Apr 2004 11:19:47 -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 48887-09 for ; Tue, 20 Apr 2004 11:19:50 -0300 (ADT) Received: from mail.astudios.com (unknown [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 5111FD1C9E1 for ; Tue, 20 Apr 2004 11:19:45 -0300 (ADT) thread-index: AcQm4v/045T4I1TUSGSZhIXZa9046w== Received: from [10.4.2.163] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.0); Tue, 20 Apr 2004 10:23:06 -0400 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 From: "Chris Hoover" To: Subject: Use of subquery causes seq scan??? Date: Tue, 20 Apr 2004 10:20:05 -0400 User-Agent: KMail/1.6.1 References: <200404151248.16067.jeremy.guthrie@berbee.com> In-Reply-To: <200404151248.16067.jeremy.guthrie@berbee.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-ID: <200404201020.05699.revoohc@sermonaudio.com> X-OriginalArrivalTime: 20 Apr 2004 14:23:07.0093 (UTC) FILETIME=[FFE5C850:01C426E2] 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/307 X-Sequence-Number: 6607 I need some help. I have a query that refuses to use the provided index an= d=20 is always sequentially scanning causing me large performance headaches. He= re=20 is the basic situation: Table A: inv_num int type char =2E =2E =2E 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=20 enable_seqscan to false before the query, I get an index scan of table b bu= t=20 still seq scan table a.=20 Is there anyway to force table a to use this index (or another) and not=20 sequentially scan the table? I'm running 7.3.4 on RedHat EL 2.1. Thanks, Chris