X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 3EBB9CD5C7 for ; Fri, 11 Jul 2003 07:10:25 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 41620-03 for ; Fri, 11 Jul 2003 04:10:11 -0300 (ADT) Received: from mail-2.zoominternet.net (unknown [63.67.120.12]) by svr1.postgresql.org (Postfix) with SMTP id BA070CD7E7 for ; Thu, 10 Jul 2003 12:17:46 -0300 (ADT) Received: (qmail 5616 invoked from network); 10 Jul 2003 15:17:58 -0000 Received: from acs-24-154-209-142.zoominternet.net (HELO gmod.lajolla) ([24.154.209.142]) (envelope-sender ) by mail-2.zoominternet.net (qmail-ldap-1.03) with SMTP for ; 10 Jul 2003 15:17:58 -0000 Subject: force the use of a particular index From: Scott Cain To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: Cold Spring Harbor Lab Message-Id: <1057850280.1451.22.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 10 Jul 2003 11:18:01 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/172 X-Sequence-Number: 2465 Hello, I am wondering if there is a way to force the use of a particular index when doing a query. I have two tables that are pretty big (each >3 million rows), and when I do a join between them the performance is generally quite poor as it does not use the indexes that I think it should use. Here is an example query: SELECT DISTINCT f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id FROM feature f, featureloc fl WHERE f.feature_id = fl.feature_id and fl.srcfeature_id = 6 and fl.fmin <= 2585581 and fl.fmax >= 2565581 and f.type_id = 219 Now, I know that if the query planner will use an index on featureloc on (srcfeature_id, fmin, fmax) that will reduce the amount of data from the featureloc table from over 3 million to at most a few thousand, and it will go quite quickly (if I drop other indexes on this table, it does use that index and completes in about 1/1000th of the time). After that, the join with the feature table should go quite quickly as well using the primary key on feature. So, the question is, is there a way I can force the query planner to use the index I want it to use? I have experimented with using INNER JOIN and changing the order of the tables in the join clause, but nothing seems to work. Any suggestions? Thanks much, Scott -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory