Re: Why do we allow indexes to contain the same column more than once?

Is there some use case for that I'm not seeing?

postgres=***# create index tti on
tt(i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i);
CREATE INDEX
Time: 15.891 ms

postgres=***# commit;
COMMIT
Time: 11.191 ms

postgres=# \d tt
Table "public.tt"
+--------+---------+-----------+
| Column | Type | Modifiers |
+--------+---------+-----------+
| i | integer | |
+--------+---------+-----------+
Indexes:
"tti" btree (i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i, i,
i, i, i, i, i, i, i, i, i, i, i, i, i, i)

postgres=# \d tti
Index "public.tti"
+--------+---------+------------+
| Column | Type | Definition |
+--------+---------+------------+
| i | integer | i |
| i1 | integer | i |
| i2 | integer | i |
| i3 | integer | i |
| i4 | integer | i |
| i5 | integer | i |
| i6 | integer | i |
| i7 | integer | i |
| i8 | integer | i |
| i9 | integer | i |
| i10 | integer | i |
| i11 | integer | i |
| i12 | integer | i |
| i13 | integer | i |
| i14 | integer | i |
| i15 | integer | i |
| i16 | integer | i |
| i17 | integer | i |
| i18 | integer | i |
| i19 | integer | i |
| i20 | integer | i |
| i21 | integer | i |
| i22 | integer | i |
| i23 | integer | i |
| i24 | integer | i |
| i25 | integer | i |
| i26 | integer | i |
| i27 | integer | i |
| i28 | integer | i |
| i29 | integer | i |
| i30 | integer | i |
| i31 | integer | i |
+--------+---------+------------+
btree, for table "public.tt"

--
greg

Hi,

On 2014-05-01 13:55:46 +0100, Greg Stark wrote:
> Is there some use case for that I'm not seeing?
>
> postgres=***# create index tti on
> tt(i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i);
> CREATE INDEX
> Time: 15.891 ms

Can be useful if different opclasses are used for the individual
columns. Other than that I am not seing much use.
But what would we gain by prohibiting it except possibly breaking
people's upgrades?

Greetings,

Andres Freund

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2014-05-01 13:55:46 +0100, Greg Stark wrote:
>> Is there some use case for that I'm not seeing?

> Can be useful if different opclasses are used for the individual
> columns. Other than that I am not seing much use.

Yeah. This is more plausible for the advanced index types like GIST,
where different opclasses might possibly do significantly different
things. We do prohibit it for unique/pkey constraints:

regression=# create table tt(f1 int, unique(f1,f1));
ERROR: column "f1" appears twice in unique constraint
LINE 1: create table tt(f1 int, unique(f1,f1));
^

which I think is per SQL spec, and anyway there would be no way that
the constraint code would select different opclasses for the same column,
so it's clearly redundant.

regards, tom lane