Re: About index - "a query or data manipulation command can use at most one index per table"

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Ying Lu <ying_lu(at)cs(dot)concordia(dot)ca>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: About index - "a query or data manipulation command can use at most one index per table"
Date: 2005-04-26 19:04:47
Message-ID: 20050426190447.GA36064@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Apr 26, 2005 at 01:58:09PM -0400, Ying Lu wrote:
>
> A question about index. It mentioned in postgresql 8.0 doc "a query or
> data manipulation command can use *at most one index* *per table*". An
> example query is:
>
> select * from A left join B using (id) where A.type='apple' and
> A.isExport=true;
>
> "id" is the primary key for both table A & B. If index (type, isExport)
> has been created for table A. In the above query, will this index works?

You can use EXPLAIN to see the query plan, including which indexes
will be used. See "Using EXPLAIN" in the "Performance Tips" chapter
of the documentation.

http://www.postgresql.org/docs/8.0/interactive/performance-tips.html#USING-EXPLAIN
http://www.postgresql.org/docs/8.0/interactive/sql-explain.html

A query won't necessarily use an index if one is available: if the
planner thinks a sequential scan will be faster than using an index,
then it won't use the index. If you want to see whether an index
scan *could* be used, then set enable_seqscan to off before running
EXPLAIN.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ragnar Hafstað 2005-04-26 19:08:25 Re: About index - "a query or data manipulation command
Previous Message Richard_D_Levine 2005-04-26 18:59:26 Re: UltraSPARC versus AMD