This page in other versions: 9.1 / 9.2 / 9.3  |  Development versions: devel

11.10. Indexes and Collations

An index can support only one collation per index column. If multiple collations are of interest, multiple indexes may be needed.

Consider these statements:

CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

The index automatically uses the collation of the underlying column. So a query of the form

SELECT * FROM test1c WHERE content > constant;

could use the index, because the comparison will by default use the collation of the column. However, this index cannot accelerate queries that involve some other collation. So if queries of the form, say,

SELECT * FROM test1c WHERE content > constant COLLATE "y";

are also of interest, an additional index could be created that supports the "y" collation, like this:

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

Comments


Nov. 9, 2011, 4:03 p.m.

If you create an index with a defined collation and you search by another one, that index wouldn't be used by this query.

For example:

CREATE INDEX ON prueba (texto COLLATE "en_US");

Then, some searchs:

palominodb=# explain select * from prueba where texto = '0.225917448755354';
QUERY PLAN
--------------------------------------------------------
Seq Scan on prueba (cost=0.00..20.49 rows=1 width=29)
Filter: (texto = '0.225917448755354'::text)
(2 rows)

palominodb=# explain select * from prueba where (texto COLLATE "en_US") = '0.225917448755354';
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using prueba_texto_idx on prueba (cost=0.00..8.27 rows=1 width=29)
Index Cond: ((texto)::text = '0.225917448755354'::text)
(2 rows)

Just be sure before create the index if you will have the expected behavior.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group