Message-ID: <55F7B9A2.4030703@sigaev.ru>
Date: Tue, 15 Sep 2015 09:24:34 +0300
From: Teodor Sigaev <teodor@sigaev.ru>
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
 rv:36.0) Gecko/20100101 Firefox/36.0 SeaMonkey/2.33.1
MIME-Version: 1.0
To: Thom Brown <thom@linux.com>, Oleg Bartunov <obartunov@gmail.com>
CC: Jim Nasby <Jim.Nasby@bluetreble.com>,
 Thomas Munro <thomas.munro@enterprisedb.com>,
 Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PROPOSAL] Covering + unique indexes.
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>
In-Reply-To: 
 <CAA-aLv6QBaTu7XHrzADMkJDkALGrrcjBO=exTJC_iWMKr4FMaQ@mail.gmail.com>
Content-Type: text/plain; charset=KOI8-R; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

>     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