Re: About index - "a query or data manipulation command

Поиск
Список
Период
Сортировка
Искать

Re: About index - "a query or data manipulation command

От:
Ragnar Hafstað <gnari@simnet.is>
Дата:
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

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

От:
Michael Fuhr <mike@fuhr.org>
Дата:
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/

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

От:
Ying Lu <ying_lu@cs.concordia.ca>
Дата:
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
FAQ