Re: Theory of operation of collation patch - Mailing list pgsql-hackers

Peter Eisentraut <peter_e@gmx.net> writes:
> On mån, 2011-03-07 at 11:43 -0500, Tom Lane wrote:
>> ... 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:

[ pokes at it some more... ]  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.

But more to the point, your examples do *not* work for me.  I can
reproduce both failing to use an index that should work, and selecting
an index that doesn't work:

d1u=# CREATE TABLE test (a text);
CREATE TABLE
d1u=# CREATE INDEX test_1 ON test (a);
CREATE INDEX
d1u=# CREATE INDEX test_d ON test (a COLLATE "de_DE");
CREATE INDEX
d1u=# CREATE INDEX test_e ON test (a COLLATE "es_ES");
CREATE INDEX
d1u=# CREATE INDEX test_f ON test (a COLLATE "fr_FR");
CREATE INDEX
d1u=# CREATE INDEX test_fz ON test ((a||'z') COLLATE "fr_FR");
CREATE INDEX
d1u=# explain select * from test order by a;                              QUERY PLAN                               
------------------------------------------------------------------------Index Scan using test_f on test
(cost=0.00..63.90rows=1310 width=32)
 
(1 row)

d1u=# explain select * from test order by a collate "fr_FR";                         QUERY PLAN
 
 
---------------------------------------------------------------Sort  (cost=90.93..94.20 rows=1310 width=32)  Sort Key:
((aCOLLATE "fr_FR"))  ->  Seq Scan on test  (cost=0.00..23.10 rows=1310 width=32)
 
(3 rows)

d1u=# set enable_seqscan TO 0;
SET
d1u=# explain select * from test order by a collate "fr_FR";                                   QUERY PLAN
                    
 
----------------------------------------------------------------------------------Sort
(cost=10000000090.93..10000000094.20rows=1310 width=32)  Sort Key: ((a COLLATE "fr_FR"))  ->  Seq Scan on test
(cost=10000000000.00..10000000023.10rows=1310 width=32)
 
(3 rows)

d1u=# explain select * from test order by a||'z';                                         QUERY PLAN
           
 
-------------------------------------------------------------------------Index Scan using test_fz on test
(cost=0.00..67.18rows=1310 width=32)
 
(1 row)

(This is in a database with encoding utf8 and lc_collate = c)
        regards, tom lane