* Jeff Janes:
I don't see the virtue of this in this case. Since the index is not
unique, why not just put the index on (a,b,c,d) and be done with it?
AFAICT, SQLite 4 encodes keys in a way that is not easily reversed
(although the encoding is injective, so it's reversible in principle).
On Thu, 2012-07-26 at 12:13 -0400, Bruce Momjian wrote:
So, do we want a TODO item about adding columns to a unique index that
will not be used for uniqueness checks?
-1 from me, at least in its current form.
At it's heart, this is about separating the constraint from the index
that enforces
On Fri, 2012-07-27 at 15:27 -0500, Merlin Moncure wrote:
The covering index/uniqueness use case adds
legitimacy to the INDEX clause of exclusion constraints IMNSHO.
Yes, I think it would be worth revisiting the idea.
One point of concern though is that (following a bit of testing)
alter
On Fri, Jul 27, 2012 at 1:27 PM, Merlin Moncure mmonc...@gmail.com wrote:
One point of concern though is that (following a bit of testing)
alter table foo add exclude using btree (id with =);
...is always strictly slower for inserts than
alter table foo add primary key(id);
This is probably
On Thu, 2012-07-26 at 12:13 -0400, Bruce Momjian wrote:
So, do we want a TODO item about adding columns to a unique index that
will not be used for uniqueness checks?
-1 from me, at least in its current form.
At it's heart, this is about separating the constraint from the index
that enforces
On Fri, Jul 27, 2012 at 12:24 PM, Jeff Davis pg...@j-davis.com wrote:
On Thu, 2012-07-26 at 12:13 -0400, Bruce Momjian wrote:
So, do we want a TODO item about adding columns to a unique index that
will not be used for uniqueness checks?
-1 from me, at least in its current form.
At it's
On Tue, Jul 17, 2012 at 06:00:37PM +0100, Simon Riggs wrote:
Either way the data in c and d are IN THE INDEX otherwise in neither
case could the data values be returned while strictly querying the index.
So the question that needs to be asked is what kind of performance increase
can be
On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian br...@momjian.us wrote:
On Tue, Jul 17, 2012 at 06:00:37PM +0100, Simon Riggs wrote:
Either way the data in c and d are IN THE INDEX otherwise in neither
case could the data values be returned while strictly querying the index.
So the
On Thu, Jul 26, 2012 at 12:25 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian br...@momjian.us wrote:
On Tue, Jul 17, 2012 at 06:00:37PM +0100, Simon Riggs wrote:
Either way the data in c and d are IN THE INDEX otherwise in neither
case could the
On Thu, Jul 26, 2012 at 12:17 PM, Robert Haas robertmh...@gmail.com wrote:
I think so. The case where you want a wide multiple column primary
key to be extended to cover that one extra commonly grabbed value is
not super common but entirely plausible. With the existing
infrastructure to get
On 28 June 2012 13:16, David E. Wheeler da...@justatheory.com wrote:
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
On Jul 17, 2012, at 5:18 PM, Simon Riggs wrote:
Now that we have index-only scans in 9.2, I'm wondering if it would make
sense to add covering index support, too, where additional, unindexed
columns are stored alongside indexed columns.
Just to be clear, the ability to have covered
On 17 July 2012 16:21, David E. Wheeler da...@justatheory.com wrote:
On Jul 17, 2012, at 5:18 PM, Simon Riggs wrote:
Now that we have index-only scans in 9.2, I'm wondering if it would make
sense to add covering index support, too, where additional, unindexed
columns are stored alongside
On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote:
CREATE INDEX ON foo (a, b, c, d);
allows
SELECT c, d FROM foo WHERE a = ? AND b = ?
to use an index only scan.
The phrase unindexed seems misleading since the data is clearly in
the index from the description on the URL you gave. And
On 17 July 2012 16:54, David E. Wheeler da...@justatheory.com wrote:
On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote:
CREATE INDEX ON foo (a, b, c, d);
allows
SELECT c, d FROM foo WHERE a = ? AND b = ?
to use an index only scan.
The phrase unindexed seems misleading since the data is
On Tue, Jul 17, 2012 at 6:08 PM, Simon Riggs si...@2ndquadrant.com wrote:
On 17 July 2012 16:54, David E. Wheeler da...@justatheory.com wrote:
Yeah, but that index is unnecessarily big if one will never use c or d
in the search. The nice thing about covering indexes as described for
SQLite 4
-Original Message-
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-
ow...@postgresql.org] On Behalf Of David E. Wheeler
Sent: Tuesday, July 17, 2012 11:55 AM
To: Simon Riggs
Cc: Pg Hackers
Subject: Re: [HACKERS] Covering Indexes
On Jul 17, 2012, at 5:32 PM, Simon
On 17 July 2012 17:41, David Johnston pol...@yahoo.com wrote:
Concretely, I would presume that the contents of a covering index could then
look like the following (a,b,c,d):
(2,1,2,A)
(2,1,5,A) -- the 5 is out of natural order but exists in the covering
part
(2,1,3,A)
Whereas PostgreSQL
On 07/17/2012 12:41 PM, David Johnston wrote:
So the question that needs to be asked is what kind of performance increase
can be had during DML (insert/update) statements and whether those gains are
worth pursuing. Since these other engines appear to allow both cases you
should be able to get
David E. Wheeler da...@justatheory.com
ca+u5nmjz33zsvqpzk-auoindxkq6elip1hgq53byodlpwfd...@mail.gmail.com writes:
On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote:
The phrase unindexed seems misleading since the data is clearly in
the index from the description on the URL you gave. And since the
Hi all,
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler da...@justatheory.com
wrote:
I don't see the virtue of this in this case. Since the index is not
unique, why not just put the index on (a,b,c,d) and be done with it?
Is there some advantage to be had in inventing a way to store c
On Fri, Jun 29, 2012 at 08:10:03AM +0200, Csaba Nagy wrote:
Hi all,
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler da...@justatheory.com
wrote:
I don't see the virtue of this in this case. Since the index is not
unique, why not just put the index on (a,b,c,d) and be done with it?
Le vendredi 6 juillet 2012 15:41:01, Bruce Momjian a écrit :
On Fri, Jun 29, 2012 at 08:10:03AM +0200, Csaba Nagy wrote:
Hi all,
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler
da...@justatheory.com wrote: I don't see the virtue of this in this
case. Since the index is not
Csaba Nagy ncsli...@googlemail.com writes:
Why not restrict it to UNIQUE indexes ?
What benefit would such a restriction provide? AFAICS it doesn't make
implementation any easier.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
On 28 June 2012 14:02, Rob Wultsch wult...@gmail.com wrote:
On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler da...@justatheory.com
wrote:
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
IRC MS SQL also allow unindexed
On Thu, Jun 28, 2012 at 7:02 AM, Rob Wultsch wult...@gmail.com wrote:
On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler da...@justatheory.com
wrote:
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
This allows the following query to do an index-only scan:
On 06/28/2012 02:16 PM, David E. Wheeler wrote:
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
This allows the
On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler da...@justatheory.com wrote:
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b)
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler da...@justatheory.com wrote:
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b)
On 06/28/2012 11:37 AM, Jeff Janes wrote:
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheelerda...@justatheory.com wrote:
Hackers,
Very interesting design document for SQLite 4:
http://www.sqlite.org/src4/doc/trunk/www/design.wiki
I'm particularly intrigued by covering indexes. For
Andrew Dunstan and...@dunslane.net writes:
On 06/28/2012 11:37 AM, Jeff Janes wrote:
On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheelerda...@justatheory.com
wrote:
I'm particularly intrigued by covering indexes. For example:
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
I don't see the
Excerpts from Tom Lane's message of jue jun 28 12:07:58 -0400 2012:
When this came up a couple weeks ago, the argument that was made for it
was that you could attach non-significant columns to an index that *is*
unique. That might or might not be a wide enough use-case to justify
adding
On Thu, Jun 28, 2012 at 12:12 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
The other question is whether such an index would prevent an update from
being HOT when the non-indexed values are touched. That could be a
significant difference.
I don't see Index-Only-Scans being something
Alvaro Herrera alvhe...@commandprompt.com writes:
The other question is whether such an index would prevent an update from
being HOT when the non-indexed values are touched.
Surely it would *have* to, whether the columns are significant or not
for uniqueness purposes. Else an index-only scan
On Thu, Jun 28, 2012 at 9:12 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Excerpts from Tom Lane's message of jue jun 28 12:07:58 -0400 2012:
When this came up a couple weeks ago, the argument that was made for it
was that you could attach non-significant columns to an index that *is*
36 matches
Mail list logo
