Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M281644=pgsql-hackers-arkari@postgresql.org>)
 id 1ZcCON-0003v9-7S
 for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Sep 2015 13:04:55 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-hackers-owner+M281644=pgsql-hackers-arkari@postgresql.org>)
 id 1ZcCOM-00041B-PI
 for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Sep 2015 13:04:54 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <jltallon@adv-solutions.net>)
 id 1ZcCN3-0002Si-Vv
 for pgsql-hackers@postgresql.org; Wed, 16 Sep 2015 13:03:34 +0000
Received: from h092-net00.csimad.doblenet.net ([213.27.130.92]
 helo=quark.amimatica.com)
 by makus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <jltallon@adv-solutions.net>) id 1ZcCMz-0005zw-LZ
 for pgsql-hackers@postgresql.org; Wed, 16 Sep 2015 13:03:32 +0000
Received: from vortex.hq.g4ii.com (182.Red-88-26-239.staticIP.rima-tde.net
 [88.26.239.182])
 by quark.amimatica.com (Postfix) with ESMTPA id 9C5266C0
 for <pgsql-hackers@postgresql.org>; Wed, 16 Sep 2015 15:03:29 +0200 (CEST)
Message-ID: <55F96896.3040409@adv-solutions.net>
Date: Wed, 16 Sep 2015 15:03:18 +0200
From: =?UTF-8?B?Sm9zw6kgTHVpcyBUYWxsw7Nu?= <jltallon@adv-solutions.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.7.0
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
Subject: Re: [PROPOSAL] Covering + unique indexes.
References: <55F2C1BC.3010109@postgrespro.ru>
 <55F2CCD0.7040608@postgrespro.ru> <55F84DF4.5030207@postgrespro.ru>
In-Reply-To: <55F84DF4.5030207@postgrespro.ru>
Content-Type: multipart/alternative;
 boundary="------------010207040200070500050805"
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

This is a multi-part message in MIME format.
--------------010207040200070500050805
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 09/15/2015 06:57 PM, Anastasia Lubennikova wrote:
> Proposal Clarification.
> I see that discussion become too complicated. So, I'd like to clarify 
> what we are talking about.
>
> [snip]
> What are we doing now:
> CREATE UNIQUE INDEX on tbl(f1,f2);
> CREATE INDEX on tbl(f1, f2, f3, f4);
>
> [snip]
> Suggestions.
> CREATE INDEX idx ON tbl (f1, f2, f3) [UNIQUE ON (f1, f2)] [INCLUDE (f4)];

Summarizing some suggestions upthread, it seems like the "best" syntax 
would be something similar to:

-- Non-unique index + "leaf" information (f4)
CREATE INDEX idx ON tbl (f1, f2, f3) [INCLUDING (f4)]

-- Unique index on f1,f2, + leaf information (f3)
CREATE UNIQUE INDEX idx ON tbl (f1, f2) [INCLUDING (f3)]

And, even:
ALTER INDEX idx INCLUDING (f4)
  ... which would trigger a REINDEX CONCURRENTLY internally ?


FWIW this would include also the functionality provided by the suggested
     CREATE INDEX idx ON tbl (f1, f2, f3) UNIQUE ON (f1, f2);

while being less verbose, IMHO.


The obvious inconvenient being that the indexes will grow a bit, so 
fewer entries will fit in memory.


Also, we don't meddle with WITH clauses (for smgr parameters or the 
like) nor USING <method> clauses.
I reckon that implementation might be a bit intrusive (requiring changes 
to most index AMs even?)



Thanks!

     / J.L.


--------------010207040200070500050805
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 09/15/2015 06:57 PM, Anastasia
      Lubennikova wrote:<br>
    </div>
    <blockquote cite="mid:55F84DF4.5030207@postgrespro.ru" type="cite">
      <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
      Proposal Clarification.<br>
      I see that discussion become too <span
        class="b-translation__text">complicated. So, I'd like to clarify
        what we are talking about.<br>
        <br>
        [snip]<br>
        What are we doing now:<br>
        CREATE UNIQUE INDEX on tbl(f1,f2);<br>
        CREATE INDEX on tbl(f1, f2, f3, f4);<br>
        <br>
        [snip]</span><br>
      Suggestions.<br>
      CREATE INDEX idx ON tbl (f1, f2, f3) [UNIQUE ON (f1, f2)] [INCLUDE
      (f4)];<br>
    </blockquote>
    <br>
    Summarizing some suggestions upthread, it seems like the "best"
    syntax would be something similar to:<br>
    <br>
    -- Non-unique index + "leaf" information (f4)<br>
    CREATE INDEX idx ON tbl (f1, f2, f3) [INCLUDING (f4)]<br>
    <br>
    -- Unique index on f1,f2, + leaf information (f3)<br>
    CREATE UNIQUE INDEX idx ON tbl (f1, f2) [INCLUDING (f3)]<br>
    <br>
    And, even:<br>
    ALTER INDEX idx INCLUDING (f4)<br>
     ... which would trigger a REINDEX CONCURRENTLY internally ?<br>
    <br>
    <br>
    FWIW this would include also the functionality provided by the
    suggested<br>
        CREATE INDEX idx ON tbl (f1, f2, f3) UNIQUE ON (f1, f2);<br>
    <br>
    while being less verbose, IMHO.<br>
    <br>
    <br>
    The obvious inconvenient being that the indexes will grow a bit, so
    fewer entries will fit in memory.<br>
    <br>
    <br>
    Also, we don't meddle with WITH clauses (for smgr parameters or the
    like) nor USING &lt;method&gt; clauses.<br>
    I reckon that implementation might be a bit intrusive (requiring
    changes to most index AMs even?)<br>
    <br>
    <br>
    <br>
    Thanks!<br>
    <br>
        / J.L.<br>
    <br>
  </body>
</html>

--------------010207040200070500050805--