From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Thomas Munro <thomas.munro@enterprisedb.com>
To: Teodor Sigaev <teodor@sigaev.ru>
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 13:39:46 +1200
Message-ID: <55F776E2.2030301@archidevsys.co.nz> (raw)
In-Reply-To: <55F73FDA.2030001@BlueTreble.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 15/09/15 09:44, Jim Nasby wrote:
> On 9/14/15 1:50 PM, Thomas Munro wrote:
>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
>> INDEX ON
>> table_name (column_name1, column_name2 ...);
>>
>>
>> I would use the first (simple) syntax and just throw an error
>> if the
>> user tries to skip a column on the UNIQUE clause.
>>
>> Seems, second option looks as more natural extension of CREATE
>> UNIQUE INDEX
>
> True, but it's awefully verbose. :( And...
>
>> It surprised me that you can INCLUDE extra columns on non-UNIQUE
>> indexes, since you could just add them as regular indexed columns for
>> the same effect. It looks like when you do that in SQL Server, the
>> extra columns are only stored on btree leaf pages and so can't be used
>> for searching or ordering. I don't know how useful that is or if we
>> would ever want it... but I just wanted to note that difference, and
>> that the proposed UNIQUE ON FIRST n COLUMNS syntax and catalog change
>> can't express that.
>
> ... we might want to support INCLUDE at some point. It enhances
> covering scans without bloating the heck out of the btree. (I'm not
> sure if it would help other index types...) So it seems like a bad
> idea to preclude that.
>
> I don't see that UNIQUE ON FIRST precludes also supporting INCLUDE.
> Presumably we could do either
>
> CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
Of the formats I've seen so far, I prefer this one.
I think using "[ALSO] INCLUDE(f4)" - might be potentially more readable
than using just "INCLUDE(f4)". even if not used, the noise word also
would help people understand that the other fields mentioned are already
covered.
If not too difficult then allowing the unique fields to be separated by
other fields could be useful - in the example allowing "UNIQUE(f1,
f3)". Especially if the index is likely to be used to CLUSTER a table,
where the order f1, f2, ... is important.
> or
> CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ... ON table (f1, f2, f3)
> INCLUDE(f4);
>
> Personally, I find the first form easier to read.
>
> Are we certain that no index type could ever support an index on (f1,
> f2, f3) UNIQUE(f1, f3)? Even if it doesn't make sense for btree,
> perhaps some other index could handle it.
--
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: GavinFlower@archidevsys.co.nz, Jim.Nasby@BlueTreble.com, thomas.munro@enterprisedb.com, teodor@sigaev.ru, a.lubennikova@postgrespro.ru
Subject: Re: [PROPOSAL] Covering + unique indexes.
In-Reply-To: <55F776E2.2030301@archidevsys.co.nz>
* 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