Including multiple fields in a Postgres table index

I'm usi开发者_C百科ng Postgres 8.4 and I'm performing a search using ILIKE. Since I'm searching in 4 columns (containing text) from that table I was wondering if it's ok to create a single index for all the 4 columns and not an index for each column.

Thank you.

---

This is a bit of a complicated topic. In general databases will not optimize a LIKE query unless it is anchored to the beginning. If you are searching across 4 columns, then this is LIKEly not the case.

http://www.postgresql.org/docs/8.4/static/indexes-types.html

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.9 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.

You may consider the full text support in postgresql if you are doing natural language queries (like a search engine)...