pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Cain <cain@cshl.org>
To: pgsql-performance@postgresql.org
Subject: force the use of a particular index
Date: 10 Jul 2003 11:18:01 -0400
Message-ID: <1057850280.1451.22.camel@localhost.localdomain> (raw)

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




view thread (12+ messages)  latest in thread

Message-ID: <1057850280.1451.22.camel@localhost.localdomain>
Permalink:  ../1057850280.1451.22.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1057850280.1451.22.camel@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: cain@cshl.org
  Subject: Re: force the use of a particular index
  In-Reply-To: <1057850280.1451.22.camel@localhost.localdomain>
  References: <1057850280.1451.22.camel@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox