From: Teodor Sigaev <teodor@sigaev.ru>
To: Thom Brown <thom@linux.com>
To: Oleg Bartunov <obartunov@gmail.com>
Cc: Jim Nasby <Jim.Nasby@bluetreble.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PROPOSAL] Covering + unique indexes.
Date: Tue, 15 Sep 2015 09:24:34 +0300
Message-ID: <55F7B9A2.4030703@sigaev.ru> (raw)
In-Reply-To: <CAA-aLv6QBaTu7XHrzADMkJDkALGrrcjBO=exTJC_iWMKr4FMaQ@mail.gmail.com>
References: <55F2C1BC.3010109@postgrespro.ru>
<55F2CCD0.7040608@postgrespro.ru>
<55F6F7AD.4080307@BlueTreble.com>
<55F70D0A.2030503@sigaev.ru>
<CAEepm=2G4jdaoXHU0PJ7MqLms88==r44z9bvLpnzjyOTDzwAEw@mail.gmail.com>
<55F73FDA.2030001@BlueTreble.com>
<CAF4Au4xxRvWSYnhao8w+eEdnniDfNdHLRBjaX7oMuGg818wi8A@mail.gmail.com>
<CAA-aLv6QBaTu7XHrzADMkJDkALGrrcjBO=exTJC_iWMKr4FMaQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
> Why not normal syntax with optional INCLUDE ?
>
> CREATE UNIQUE INDEX ON table (f1,f2,f3) INCLUDE (f4)
>
>
> That's not the same thing. Then you're including f3 in the unique
> constraint, which you may not want for uniqueness purposes, but may want
> in the index for sorting. But then saying that, if f1 and f2 are
> unique, you'd only get 1 value of f3 for each combination of f1 and f2,
> so sorting probably isn't useful. You might as well only INCLUDE f3
> rather than have it in the multi-column index for sorting. So to adjust
> your example:
>
> CREATE UNIQUE INDEX ON table (f1,f2) INCLUDE (f3, f4);
>
> Is there a scenario anyone can think of where f3 here:
>
> CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
>
> would be advantageous outside of INCLUDE?
>
> Out of curiosity, why is this only being discussed for unique indexes?
> What if you want additional columns included on non-unique indexes?
Because there is no difference for non-unique indexes between (f1,f2,f3)
and (f1, f2) INCLUDE (f3). In second case we just got index with
unordered f3 column.
Oh no, it's possible that f3 column type has not btree operator class...
If we want to support this case then intervention in planner will be a
bit invasive.
BTW, I don't see in foreseen future another unique access methods.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: teodor@sigaev.ru, thom@linux.com, obartunov@gmail.com, Jim.Nasby@bluetreble.com, thomas.munro@enterprisedb.com, a.lubennikova@postgrespro.ru
Subject: Re: [PROPOSAL] Covering + unique indexes.
In-Reply-To: <55F7B9A2.4030703@sigaev.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox