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.90rows=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.90rows=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.90rows=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.90rows=437 width=32)  Index Cond: (a > ('foo'::text COLLATE "fr_FR"))
 
(2 rows)