Subject: [PROPOSAL] Covering + unique indexes.
References: <55F2C1BC.3010109@postgrespro.ru>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Message-ID: <55F2CCD0.7040608@postgrespro.ru>
Date: Fri, 11 Sep 2015 15:45:04 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <55F2C1BC.3010109@postgrespro.ru>
Content-Type: multipart/alternative;
 boundary="------------030502020706030202000606"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

Hi, hackers!

Use case:
Index-only scans is a wonderful feature that allows to speed up select 
queries of indexed columns.
Therefore some users want to create multicolumn indexes on columns which 
are queried often.
But if there's unique constraint on some column, they have to maintain 
another unique index.
Even if the column is already in covering index.
This adds overhead to data manipulation operations and database size.

I've started work on a patch that allows to combine covering and unique 
functionality.
The main idea is to allow user create multicolumn indexes with a 
definite number of unique columns.
For example (don't mind SQL syntax here, please):
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c2);
Created index has three columns, but only two of them have unique 
constraint.

This idea has obvious restriction. We can set unique only for first 
index columns.
There is no clear way to maintain following index.
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);

So I suggest following syntax:
CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON 
table_name (column_name1, column_name2 ...);

Examples:
CREATE UNIQUE INDEX ON table_name (c1, c2, c3); // (c1,c2, c3) must be 
UNIQUE. That's how it works now.

CREATE UNIQUE ON FIRST COLUMN INDEX ON table_name (c1, c2, c3); // (c1) 
must be UNIQUE
CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ON table_name (c1, c2, c3); // 
(c1,c2) must be UNIQUE
CREATE UNIQUE ON FIRST 3 COLUMNS INDEX ON table_name (c1, c2, c3); // 
(c1,c2, c3) must be UNIQUE

Next issue is pg_index changes.
Now there's only a boolean flag

  * bool indisunique; /* is this a unique index? */

But new algorithm requires to store a single number

  * unit16n_unique_columns; /* number of first columns of index which
    has unique constrains. */

I think, that numbers of all attributes themselves are not needed. Is it 
right?

I'd like to see your suggestions about syntax changes.
And of course any other comments are welcome.

-- 
Anastasia Lubennikova
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company


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

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    Hi, hackers!<br>
    <div class="moz-forward-container"> <br>
      Use case:<br>
      Index-only scans is a wonderful feature that allows to speed up
      select queries of indexed columns.<br>
      Therefore some users want to create multicolumn indexes on columns
      which are queried often.<br>
      But if there's unique constraint on some column, they have to
      maintain another unique index.<br>
      Even if the column is already in covering index.<br>
      This adds overhead to data manipulation operations and database
      size.<br>
      <br>
      I've started work on a patch that allows to combine covering and
      unique functionality.<br>
      The main idea is to allow user create multicolumn indexes with a
      definite number of unique columns.<br>
      For example (don't mind SQL syntax here, please):<br>
      CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c2);<br>
      Created index has three columns, but only two of them have unique
      constraint.<br>
      <br>
      This idea has obvious restriction. We can set unique only for
      first index columns.<br>
      There is no clear way to maintain following index.<br>
      CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);<br>
      <br>
      So I suggest following syntax:<br>
      CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX
      ON table_name (column_name1, column_name2 ...);<br>
      <br>
      Examples:<br>
      CREATE UNIQUE INDEX ON table_name (c1, c2, c3); // (c1,c2, c3)
      must be UNIQUE. That's how it works now.<br>
      <br>
      CREATE UNIQUE ON FIRST COLUMN INDEX ON table_name (c1, c2, c3); //
      (c1) must be UNIQUE<br>
      CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ON table_name (c1, c2, c3);
      // (c1,c2) must be UNIQUE<br>
      CREATE UNIQUE ON FIRST 3 COLUMNS INDEX ON table_name (c1, c2, c3);
      // (c1,c2, c3) must be UNIQUE<br>
      <br>
      <div class="line">Next issue is pg_index changes.<br>
        Now there's only a boolean flag<br>
        <div class="line">
          <ul>
            <li><span class="keywordtype">bool</span> indisunique; <span
                class="comment">/* is this a unique index? */</span></li>
          </ul>
          <span class="comment">But new algorithm requires to store a
            single number<br>
          </span>
          <ul>
            <li>unit16<span class="comment"> n_unique_columns; /* number
                of first columns of index which has unique constrains.
                */<br>
              </span></li>
          </ul>
          I think, that numbers of all attributes themselves are not
          needed. Is it right?<br>
        </div>
      </div>
      <br>
      I'd like to see your suggestions about syntax changes. <br>
      And of course any other comments are welcome.<br>
      <pre class="moz-signature" cols="72">-- 
Anastasia Lubennikova
Postgres Professional: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>
    </div>
  </body>
</html>

--------------030502020706030202000606--