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

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/