Subject: Re: [PROPOSAL] Covering + unique indexes.
To: Thomas Munro <thomas.munro@enterprisedb.com>, Teodor Sigaev
 <teodor@sigaev.ru>
References: <55F2C1BC.3010109@postgrespro.ru>
 <55F2CCD0.7040608@postgrespro.ru> <55F6F7AD.4080307@BlueTreble.com>
 <55F70D0A.2030503@sigaev.ru>
 <CAEepm=2G4jdaoXHU0PJ7MqLms88==r44z9bvLpnzjyOTDzwAEw@mail.gmail.com>
CC: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, pgsql-hackers
 <pgsql-hackers@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <55F73FDA.2030001@BlueTreble.com>
Date: Mon, 14 Sep 2015 16:44:58 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0)
 Gecko/20100101 Thunderbird/38.1.0
MIME-Version: 1.0
In-Reply-To: 
 <CAEepm=2G4jdaoXHU0PJ7MqLms88==r44z9bvLpnzjyOTDzwAEw@mail.gmail.com>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:23
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Sep 2015 21:45:07.7069 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0684
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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);
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.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers