Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZbjXq-0007z8-QS for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Sep 2015 06:16:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZbjXq-0005ex-CI for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Sep 2015 06:16:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZbjXp-0005e8-3H for pgsql-hackers@postgresql.org; Tue, 15 Sep 2015 06:16:45 +0000 Received: from hook.sigaev.ru ([93.180.27.50]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZbjXm-00025w-BK for pgsql-hackers@postgresql.org; Tue, 15 Sep 2015 06:16:44 +0000 Received: from zorg.sigaev.ru (89-178-7-103.broadband.corbina.ru [89.178.7.103]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: teodor) by hook.sigaev.ru (Postfix) with ESMTPSA id 71239B1918F; Tue, 15 Sep 2015 09:16:41 +0300 (MSK) Message-ID: <55F7B7C9.6090109@sigaev.ru> Date: Tue, 15 Sep 2015 09:16:41 +0300 From: Teodor Sigaev 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: Jim Nasby , Thomas Munro CC: Anastasia Lubennikova , pgsql-hackers Subject: Re: [PROPOSAL] Covering + unique indexes. References: <55F2C1BC.3010109@postgrespro.ru> <55F2CCD0.7040608@postgrespro.ru> <55F6F7AD.4080307@BlueTreble.com> <55F70D0A.2030503@sigaev.ru> <55F73FDA.2030001@BlueTreble.com> In-Reply-To: <55F73FDA.2030001@BlueTreble.com> Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org > CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4); I don't see an advantage this form. What is f3 column? just order? and f4 will not be used for compare? At least now it requires additional checks that UNIQUE() fields are the same as first columns in definition. Non ordering field f4 will require invasive intervention in planner because now it believes that all columns in btree are ordered. I agree, that form CREATE UNIQUE INDEX i ON t (f1, f2, f3) INCLUDE (f4) is clear. f4 will be used in row compare and actually planner will be able to use it as unique index (f1, f2, f3) with additional f4 or as as unique index (f1, f2, f3, f4), because uniqueness on (f1, f2, f3) gives warranty of uniqueness on (f1, f2, f3, f4) -- 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