PostgreSQL index on string type

Question

How is PostgreSQL indexing strings and varchars?

Is it indexing the full "string" or only parts of it?

Until now I've assumed that it's using the full string, but never really got it verified.

Erwin Brandstetter · Accepted Answer · 2012-03-23 20:26:51Z

8

It uses the whole string. If you want to use only a part of a string you can always use an index on an expression. Like:

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

left() requires PostgreSQL 9.1 or later. For older versions:

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

answered Mar 14, 2012 at 7:12

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Answer 1