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

Hello List,

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?

Thanks a lot,
Emi

On Tue, 2005-04-26 at 13:58 -0400, Ying Lu wrote:

> 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?

simplest is just to do an explain.

explain select * from A left join B using (id) where A.type='apple' and
  A.isExport=true;

gnari

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/