Re: Theory of operation of collation patch

On mån, 2011-03-07 at 11:43 -0500, Tom Lane wrote:
> Is there any documentation of $SUBJECT? Because the more I look at
> this patch the more I think it's misdesigned; either that or I
> fundamentally misunderstand what it's trying to do. I complained
> yesterday about why the planner wasn't using indcollation to identify
> the sort order of an index. I now think that the reason it doesn't
> obviously fail to fail is that indcollation is dead code, and so is
> approximately 99% of what you added to the planner, because two
> expressions that are equal() must necessarily have the same collation
> property. Tracking the collation as a separate property of a pathkey
> is thus a useless activity. If this conclusion isn't correct, please
> explain why not.

I'll have to check into these details, but here is a test case that
shows that it's doing something with the collation of an index:

DROP TABLE test;
CREATE TABLE test (a text);
CREATE INDEX test_1 ON test (a);
CREATE INDEX test_d ON test (a COLLATE "de_DE");
CREATE INDEX test_e ON test (a COLLATE "es_ES");
CREATE INDEX test_f ON test (a COLLATE "fr_FR");
SET enable_seqscan TO off;
SET enable_bitmapscan TO off;
EXPLAIN SELECT * FROM test WHERE a > 'foo';
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_1 on test (cost=0.00..51.90 rows=437 width=32)
Index Cond: (a > 'foo'::text)
(2 rows)

EXPLAIN SELECT * FROM test WHERE a > 'foo' COLLATE "de_DE";
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_d on test (cost=0.00..51.90 rows=437 width=32)
Index Cond: (a > ('foo'::text COLLATE "de_DE"))
(2 rows)

EXPLAIN SELECT * FROM test WHERE a > 'foo' COLLATE "es_ES";
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_e on test (cost=0.00..51.90 rows=437 width=32)
Index Cond: (a > ('foo'::text COLLATE "es_ES"))
(2 rows)

EXPLAIN SELECT * FROM test WHERE a > 'foo' COLLATE "fr_FR";
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_f on test (cost=0.00..51.90 rows=437 width=32)
Index Cond: (a > ('foo'::text COLLATE "fr_FR"))
(2 rows)