Postgresql: Can a column have several indexes on it and a different one be used depending on the purpose

I am thinking of starting to use a PostgreSQL database.

I often have to do different things with the the same data. For example sometimes I have to search for similar strings with LIKE and sometimes I have to check whether a value does or doesn't exist.

In the latter c开发者_如何学Goase a Hash index would be best. In the former case, I'm not sure which is best, but I am pretty sure it's not a Hash.

Is it possible to create two different types of index on the same data and then tell the DB which to use as part of an SQL statement.

I'd be interested in any information that is specific to postgresql or relates to the capabilities of relational DMSs in general.

EDIT: My title was incorrect: I changed the word hashes to indexes

It is possible to have multiple indexes, and Postgres will automatically use the one it thinks is best suited for your query.

Postgres will use the best index it can and the overhead of checking which index to use is minimal - it shouldn't even be measurable. You can use prepared statements to eliminate it completely. Far more important is a time needed to manage additional index while inserting and updating data.

So if your table changes often you should limit the number of indexes to minimum.

If your table changes are rare or can be slow then you can create as many indexes as you like. For example in not yet released PostgreSQL 9.1 (but there's second beta available now, suitable for development) you can speed up LIKE searches significantly at the cost of slow updates/inserts.