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

On Tue, 2023-11-14 at 17:15 +0100, Peter Eisentraut wrote:
> On 14.11.23 02:58, Jeff Davis wrote:
> > If the user just wants PK/FK constraints, and equality lookups,
> > then an
> > index with the "C" collation makes a lot of sense to serve those
> > purposes.
>
> The problem is that the user has no way to declare whether they just
> want this.

We should add a way to declare that a primary key should create an
index in a particular collation. We need to be careful not to interfere
with the SQL standard, but other than that, I think this is non-
controversial.

>   The default assumption is that you get a btree and that is
> useful for range queries.

As I've said elsewhere in this thread, I think the benefit of these
pathkeys are overstated, and the costs of providing those pathkeys with
an index (performance and corruption risk) are understated.

That being said, obviously we don't want to make any sudden change to
the default behavior that would regress lots of users. But there's lots
of stuff we can do that is not so radical.

>   If the user just wants equality lookups, they
> could use a hash index.

That's a good point, and we should probably support hash indexes for
primary keys. But I don't see a reason to push users toward hash
indexes if they aren't already inclined to use hash over btree. Btree
indexes in the "C" collation work just fine if we fix a planner issue
or two.

Regards,
Jeff Davis