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

Vivek Khera <khera@kcilink.com> writes:
> Are any of these indexes redundant:

>  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?

Any of the three indexes can be used for a search on owner_id alone, so
yeah, user_list_owner_id is redundant.  It would be marginally faster to
use user_list_owner_id for such a search, just because it's physically
smaller than the other two indexes, but against that you have to balance
the extra update cost of maintaining the additional index.

Also, I can imagine scenarios where even a pure SELECT query load could
find the extra index to be a net loss: if you have a mix of queries that
use two or all three indexes, and the indexes don't fit in kernel disk
cache but just one or two would, then you'll lose on extra I/O as the
indexes compete for cache space.  Not sure how likely that scenario is,
but it's something to think about.

            regards, tom lane