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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund <andres@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers