PostgreSQL index for row type

Question

How are PostgreSQL strings and varchars indexed?

Is it indexing the entire "string" or just parts of it?

So far, I assumed it was using the full string, but never tested it.

+3

peitur 14 Mar '12 at 7:00

1 answer

Erwin Brandstetter · Accepted Answer · 2012-03-14T07:12:34+0000

It uses the entire string. If you only want to use part of a string, you can always use an index for the expression . How:

CREATE INDEX some_idx ON tbl (left(col, 10));

left()

requires PostgreSQL 9.1 or newer. For older versions:

CREATE INDEX some_idx ON tbl (substr(col, 1,10));