> On Fri, Nov 14, 2008 at 12:57:32PM +0800, Phoenix Kiula wrote:
>> 1. If I have a unique index on (user_id, url_encrypted), then will
>> queries asking only for user_id also use this index? Or should i
>> simply have separate indexes on user_id and url_encrypted? I vaguely
>> recall reading somewhere that compound indexes may have been useful in
>> MySQL but according to PG's more advanced planner having two separate
>> indexes on the columns works better.
>
> Yes. Maybe. If you build a combined index (user_id, url_encrypted) then
> it can't be used in query that only look for url_encrypted. So it
> depends on your queries. If you want to be able to search for
> url_encrypted by itself sometimes, it might be an idea to have two
> indexes.
Not true since 8.1 - according to docs:
A multicolumn B-tree index can be used with query conditions that involve
any subset of the index's columns, but the index is most efficient when
there are constraints on the leading (leftmost) columns.
Sure, the index is most efficient for leftmost columns, but it may be used
for any subset.
See http://www.postgresql.org/docs/8.2/static/indexes-multicolumn.html
regards
Tomas Vondra