Re: [HACKERS] Covering Indexes

2012-08-25 Thread Florian Weimer
* 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).

Re: [HACKERS] Covering Indexes

2012-07-31 Thread Jeff Davis
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

Re: [HACKERS] Covering Indexes

2012-07-28 Thread Jeff Davis
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

Re: [HACKERS] Covering Indexes

2012-07-28 Thread Jeff Janes
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

Re: [HACKERS] Covering Indexes

2012-07-27 Thread Jeff Davis
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

Re: [HACKERS] Covering Indexes

2012-07-27 Thread Merlin Moncure
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

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Bruce Momjian
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

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Merlin Moncure
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

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Robert Haas
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

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Merlin Moncure
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
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);

Re: [HACKERS] Covering Indexes

2012-07-17 Thread David E. Wheeler
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread David E. Wheeler
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Vik Reykja
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread David Johnston
-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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Andrew Dunstan
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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Tom Lane
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

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Csaba Nagy
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

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Bruce Momjian
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?

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Cédric Villemain
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

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Tom Lane
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)

Re: [HACKERS] Covering Indexes

2012-06-30 Thread Thomas Munro
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

Re: [HACKERS] Covering Indexes

2012-06-29 Thread Eric McKeeth
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Andreas Joseph Krogh
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Rob Wultsch
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)

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Jeff Janes
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)

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Andrew Dunstan
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Tom Lane
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Alvaro Herrera
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Aidan Van Dyk
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Tom Lane
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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Jeff Janes
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*