Including multiple fields in a Postgres table index

Question

I'm using 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.

gahooa · Accepted Answer · 2011-03-09 16:01:39Z

1

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)...

answered Mar 9, 2011 at 16:01

gahooa

138k14 gold badges101 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Or wait für 9.1 which will be able to use an index for LIKE and ILIKE: depesz.com/index.php/2011/02/19/…

Collectives™ on Stack Overflow

Including multiple fields in a Postgres table index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related