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

Vivek Khera <khera(at)kcilink(dot)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