Re: [ADMIN] Q: Structured index - which one runs faster?

On 23 May 2003 11:09:00 -0400, Vivek Khera <khera@kcilink.com> wrote:
> CREATE UNIQUE INDEX user_list_id_email ON user_list (owner_id,user_email);
> CREATE INDEX user_list_owner_id ON user_list (owner_id);
> CREATE INDEX user_list_oid_created ON user_list (owner_id,user_created);
>
>In particular, is user_list_owner_id redundant to
>user_list_oid_created?

In theory yes, but in practice it depends ...

>  Will the latter be used for queries such as
>
> SELECT user_fname from user_list where owner_id=34

All other things being equal, the planner tends to estimate higher
costs for the multi column index.  This has to do with its attempt to
adjust correlation for the additional index columns.  So unless the
physical order of tuples is totally unrelated to owner_id, I'd expect
it to choose the single column index.

>If so, I can drop the owner_id index.

If the planner estimates the cost for an user_list_id_email or
user_list_oid_created index scan lower than for a seq scan, you will
notice no difference.

But under unfortunate circumstances it might choose a seq scan ...

Servus
 Manfred