From: José Luis Tallón <jltallon@adv-solutions.net>
To: pgsql-hackers@postgresql.org
Subject: Re: [PROPOSAL] Covering + unique indexes.
Date: Wed, 16 Sep 2015 15:03:18 +0200
Message-ID: <55F96896.3040409@adv-solutions.net> (raw)
In-Reply-To: <55F84DF4.5030207@postgrespro.ru>
References: <55F2C1BC.3010109@postgrespro.ru>
<55F2CCD0.7040608@postgrespro.ru>
<55F84DF4.5030207@postgrespro.ru>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jltallon@adv-solutions.net
Subject: Re: [PROPOSAL] Covering + unique indexes.
In-Reply-To: <55F96896.3040409@adv-solutions.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox