Re: Why do indexes and sorts use the database collation?

Hi,

On 2023-11-10 16:03:16 -0800, Jeff Davis wrote:
> An "en_US" user doing:
>
> CREATE TABLE foo(t TEXT PRIMARY KEY);
>
> is providing no indication that they want an index tailored to their
> locale. Yet we are creating the index with the "en_US" collation and
> therefore imposing huge performance costs (something like 2X slower
> index build time than the "C" locale), and also huge dependency
> versioning risks that could lead to index corruption and/or wrong
> results.

I guess you are arguing that the user didn't intend to create an index here? I
don't think that is true - users know that pkeys create indexes. If we used C
here, users would often need to create a second index on the same column using
the actual database collation - I think we'd very commonly end up with
complaints that the pkey index doesn't work, because it's been automatically
created with a different collation than the column.

Also, wouldn't the intent to use a different collation for the column be
expressed by changing the column's collation?

> Similarly, a user doing:
>
> SELECT DISTINCT t FROM bar;
>
> is providing no indication that they care about the collation of "t"
> (we are free to choose a HashAgg which makes no ordering guarantee at
> all). Yet if we choose Sort+GroupAgg, the Sort will be performed in the
> "en_US" locale, which is something like 2X slower than the "C" locale.

OTOH, if we are choosing a groupagg, we might be able to implement that using
an index, which is more likey to exist in the databases collation. Looks like
we even just look for indexes that are in the database collation.

Might be worth teaching the planner additional smarts here.

> Thoughts?

I seriously doubt its a good idea to change which collations primary keys use
by default. But I think there's a decent bit of work we could do in the
planner, e.g:

- Teach the planner to take collation costs into account for costing - right
now index scans with "C" cost the same as index scans with more expensive
collations. That seems wrong even for equality lookups and would make it
hard to make improvements to prefer cheaper collations in other situations.

- Teach the planner to use cheaper collations when ordering for reasons other
than the user's direct request (e.g. DISTINCT/GROUP BY, merge joins).

I think we should also explain in our docs that C can be considerably faster -
I couldn't find anything in a quick look.

Greetings,

Andres Freund