On 04/17/2002 01:44:46 PM Michael Loftis wrote:
> In many of the cases where it is a primary key it is also there to
> ensure fast lookups when referenced as a foreign key.  Or for joins.

Don't know if the optimizer takes this into consideration, but a query that uses a primary and/or unique key in the where-clause, should always choose to use the related indices (assuming the table size is above a certain threshold). Since a primary key/unique index always restricts the resultset to a single row.....

Somebody else mentioned that after creating an index, he still had to run analyze in order to get the optimizer to choose to use the index. I thought that 'create index' also updated pg_stats?

Maarten

----

Maarten Boekhold, maarten.boekhold@reuters.com

Reuters Consulting
Dubai Media City
Building 1, 5th Floor
PO Box 1426
Dubai, United Arab Emirates
tel:+971(0)4 3918300 ext 249
fax:+971(0)4 3918333
mob:+971(0)505526539


------------------------------------------------------------- ---
        Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of  the  individual
sender,  except  where  the sender specifically states them to be
the views of Reuters Ltd.


Thu, 18 Apr 2002 11:17:34 +0400
Maarten.Boekhold@reuters.com
Re: Index Scans become Seq Scans after VACUUM ANALYSE
On Thu, 18 Apr 2002 Maarten.Boekhold@reuters.com wrote:

> 
> On 04/17/2002 01:44:46 PM Michael Loftis wrote:
> > In many of the cases where it is a primary key it is also there to
> > ensure fast lookups when referenced as a foreign key. ï¿½Or for joins.
> 
> Don't know if the optimizer takes this into consideration, but a query that uses a primary and/or unique key in the where-clause, should always choose to use
> the related indices (assuming the table size is above a certain threshold). Since a primary key/unique index always restricts the resultset to a single row.....

I don't think so.

eg. table with primary key "pk", taking values from 1 to 1000000 (so
1000000 records)

select * from table where pk > 5

should probably not use the index ...

Cheers
Tycho

-- 
Tycho Fruru			tycho.fruru@conostix.com
"Prediction is extremely difficult. Especially about the future."
  - Niels Bohr


Thu, 18 Apr 2002 10:41:15 +0200 (CEST)
tycho@fruru.com
Re: Index Scans become Seq Scans after VACUUM ANALYSE