Обсуждение: How to force an Index ?

Hi List,

     I'm looking for a way to force an Index to be used in a SQL query. In a
Oracle SQL query it would be like:

    Select /*+ my_index*/ * from my_table where my_name="rhaoni";

   So it would use the index my_index to execute the query. How can I make this
in a PostgreSQL SQL query.

Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122

On Tue, Aug 26, 2003 at 16:11:56 -0300,
  Rhaoni Chiu Pereira <rhaoni@sistemica.info> wrote:
> Hi List,
>
>      I'm looking for a way to force an Index to be used in a SQL query. In a
> Oracle SQL query it would be like:
>
>     Select /*+ my_index*/ * from my_table where my_name="rhaoni";
>
>    So it would use the index my_index to execute the query. How can I make this
> in a PostgreSQL SQL query.

There isn't a way to force specific indexes to be used. You can raise the
cost of sequential scans so that index scans will almost always be used
if they are possible. Use: set enable_seqscan = false;

In general it is best to use this only to find out why an index scan isn't
being used and then potentially change some cost functions so that the
proper plan is chosen.

On Tue, Aug 26, 2003 at 17:48:38 -0300,
  Rhaoni Chiu Pereira <rhaoni@sistemica.info> wrote:
> when I need for just one query a especific index to be used the only way is to
> raise the costs ?

There is no way to say to use a specific index. You can turn off some plans
(and raise the cost of sequential scans, since that is sometimes the only
way to do something) or you can adjust the relative costs of such things
as cpu time and disk i/o. Making sure you have done an analyze and that
you have detailed enough statistics for the planner is also important.

P.S. You should keep replies on the list.