On mån, 2011-03-07 at 12:52 -0500, Tom Lane wrote:
> It looks like indcollation is acting as a
> substitute for including a CollateClause in the index key expression,
> which doesn't seem like a particularly good tradeoff considering all
> the overhead you must introduce into the default case.
Conceptually, this characterization is correct. But assuming you do
away with the indcollation column and instead create expression indexes
on (col COLLATE "x"), you'd then have an interesting time matching those
expressions to expressions in the query when choosing an index.
Example:
CREATE TABLE test (a text);
CREATE INDEX test_idx ((a COLLATE "foo")); -- expression index
-- easy to choose the index here
SELECT * FROM test WHERE a COLLATE "foo" > 'x';
-- not so easy here
SELECT * FROM test WHERE a > 'x' COLLATE "foo";
-- more difficult
CREATE TABLE test2 (b text COLLATE "foo");
SELECT * FROM test WHERE x > (SELECT b FROM test2 WHERE something);
Note that this situation already exists: If you create a plain column
index using CREATE INDEX test_idx (a COLLATE "foo"), all of the above
cases should pick up the index; if you create an expression index using
CREATE INDEX test_idx ((a COLLATE "foo")), they won't, except the first
one. (That is modulo your earlier demonstration that it apparently
doesn't work at all for you, but I'm just saying in case you want to try
out various ways of changing this.)