I don't know how the primary key checking and the index scan are related in postgresql (but I am keen to learn more
aboutit).
From Sybase ASA (SQL Anywhere) I know, that these two functions (pk checking, index search) are decoupled.
(that means even a primary key without an index is possible. This makes only sense in tables with a few rows).
The pg-planner takes the current number of datasets to calculate a query plan to reach the best performance.
If the number of datasets changes (and the user/db-programmer usually knows if it changes) the planner sometimes make
wrongdecisions.
Then I have to execute a "vacuum analyze" or reconnect in case of foreign key checking.
I would like to tune postgresql to use an index in any case if it is available.
On Fri, 05 Oct 2001 18:01:08 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andreas Wernitznig <andreas@insilico.com> writes:
> > Why don't you skip the automatic index creation for primary keys
>
> And how then would we enforce uniqueness of the primary key?
>
> > and let the user decide to create an index, that should be used in any
> > case, regardless what the query planner recommends ?
>
> What? AFAIR the planner does not even *know* whether an index is
> associated with a primary key. It certainly does not give such an
> index any special preference.
>
> regards, tom lane
>