Re: Improving the names generated for indexes on expressions

On Monday, September 15, 2025, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> In the wake of the discussion around bug #18959 [1], here is
> a modest proposal for improving the names we pick for expression
> indexes. The commit message explains the details, but this
> example should give the flavor:
>
> postgres=# create table mytab (f1 int, f2 text, f3 text);
> CREATE TABLE
> postgres=# create index on mytab(abs(f1 + 1));
> CREATE INDEX
> postgres=# create index on mytab((f2 || f3));
> CREATE INDEX
> postgres=# \d mytab
> Table "public.mytab"
> Column | Type | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
> f1 | integer | | |
> f2 | text | | |
> f3 | text | | |
> Indexes:
> "mytab_abs_f1_+_1_idx" btree (abs(f1 + 1))
> "mytab_f2_||_f3_idx" btree ((f2 || f3))
>
> Formerly you got:
>
> "mytab_abs_idx" btree (abs(f1 + 1))
> "mytab_expr_idx" btree ((f2 || f3))
>
> There's plenty of room for differing opinions about how to do this,
> so have at it.
>

If there are no function names present, output “expr” in lieu of a function
name. Then just output any columns that are present. No operators, no
constants. If multiple functions, exist output just the first one
encountered. I’d make an exception for a boolean constant and include
true/false as well.

mytab_abs_f1_idx
mytab_expr_f2_f3_idx

I fear consistently exceeding 63 bytes of identifier length if we choose to
display the entire expression in the name. And I find it unpleasant to
read, which is generally not good for a name - though index names are not
as visible so it’s not as strong a dislike. This seems like a reasonable
compromise that is likely to communicate the most salient aspects of an
expression. It does detract from the emphasis on operators we tend to
have, but it exactly those that make the name unpleasant.

David J.