The query optimizer uses the index only if enough data are present in the table.
If only a few data are available a Seq Scan performs better and is therefore used.
Further one of the problems (which is hopfully solved in version 7.2) is that the query optimizer used for primary
keys/foreignkeys is not updated for an uninterrupted database connection.
E.g.:
If have an empty database "Seq Scan" is used in any case. Then I connect to the database, fill in some data, and
executean "VACUUM ANALYZE" (which updates the statistics for the query optimizer).
If I don't disconnect from the database but use the first connection again, the database still uses the (now) slower
"seqscan" for "primary/foreign key" checking. In this case the query optimizer statistics are not updated for
establishedconnections.
My suggestion and question to the postgresql-programmers is now:
Why don't you skip the automatic index creation for primary keys and let the user decide to create an index,
that should be used in any case, regardless whether the table is full or empty ?
Andreas
On Fri, 5 Oct 2001 03:21:01 -0400 (EDT)
pgsql-bugs@postgresql.org wrote:
> JoE Olcsak (joe78@freemail.hu) reports a bug with a severity of 4
> The lower the number the more severe it is.
>
> Short Description
> Index using problem
>
> Long Description
> I have a problem when using indexes under PostgreSQL 7.1.1 ...
>
> The problem is :
>
> I have a field of table ... This is an INT4 type field ... and I
> created an index for this field like :
> create index idx_of_field on cim(utod_cim_id);
> ... and I executed a simple query ...
>
> explain select * from cim where utod_cim_id=0;
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on cim (cost=0.00..6555.41 rows=253633 width=118)
>
> EXPLAIN
>
> The query optimizer does not use my index..
>
> I'm executed vacuum analyze command for this table but this not helps me ...
>
> Then I created another index for this field:
>
> create index idx_of_field2 on cim(int4(utod_cim_id));
>
> ... and I executed another simple query :
>
> explain select * from cim where int4(utod_cim_id)=0;
> NOTICE: QUERY PLAN:
>
> Index Scan using idx_of_field2 on cim (cost=0.00..6499.70 rows=2536 width=118)
>
> EXPLAIN
>
> What is wrong in the first case ?
>
>
>
> Sample Code
>
>
> No file was uploaded with this report
>
