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). The

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 Janes
On Fri, Jul 27, 2012 at 1:27 PM, Merlin Moncure 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 because it

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 t

Re: [HACKERS] Covering Indexes

2012-07-27 Thread Merlin Moncure
On Fri, Jul 27, 2012 at 12:24 PM, Jeff Davis 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 heart, this i

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-26 Thread Merlin Moncure
On Thu, Jul 26, 2012 at 12:17 PM, Robert Haas 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 the advantages

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Robert Haas
On Thu, Jul 26, 2012 at 12:25 PM, Merlin Moncure wrote: > On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian 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 retur

Re: [HACKERS] Covering Indexes

2012-07-26 Thread Merlin Moncure
On Thu, Jul 26, 2012 at 11:13 AM, Bruce Momjian 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 questi

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

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Tom Lane
"David E. Wheeler" 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 >> index is non-unique, I don't see any gap between Postgres and >> SQLliite4.

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 Simon Riggs
On 17 July 2012 17:41, David Johnston 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 would

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 > >

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Vik Reykja
On Tue, Jul 17, 2012 at 6:08 PM, Simon Riggs wrote: > On 17 July 2012 16:54, David E. Wheeler 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 and implemented in MSSQL is that you

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
On 17 July 2012 16:54, David E. Wheeler 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 c

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 yo

Re: [HACKERS] Covering Indexes

2012-07-17 Thread Simon Riggs
On 17 July 2012 16:21, David E. Wheeler 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 indexed colum

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 28 June 2012 13:16, David E. Wheeler 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); > > This allows t

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Tom Lane
Csaba Nagy 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) To make changes to your

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 > > > wrote: I don't see the virtue of this in this > > > case. Since the index is not unique, why no

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 > > 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

Re: [HACKERS] Covering Indexes

2012-07-06 Thread Csaba Nagy
Hi all, > On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler > 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 and d > in the inde

Re: [HACKERS] Covering Indexes

2012-06-30 Thread Thomas Munro
On 28 June 2012 14:02, Rob Wultsch wrote: > On Thu, Jun 28, 2012 at 8:16 AM, David E. Wheeler > 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 columns in the index. For what

Re: [HACKERS] Covering Indexes

2012-06-29 Thread Eric McKeeth
On Thu, Jun 28, 2012 at 7:02 AM, Rob Wultsch wrote: > On Thu, Jun 28, 2012 at 8:16 AM, 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". Fo

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Jeff Janes
On Thu, Jun 28, 2012 at 9:12 AM, Alvaro Herrera 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* >> unique.  That mi

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Tom Lane
Alvaro Herrera 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 gives the wrong value afte

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Aidan Van Dyk
On Thu, Jun 28, 2012 at 12:12 PM, Alvaro Herrera 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 that will be used in "hi

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 Tom Lane
Andrew Dunstan writes: > On 06/28/2012 11:37 AM, Jeff Janes wrote: >> On Thu, Jun 28, 2012 at 5:16 AM, David E. Wheeler >> 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 virtue of this in this ca

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. 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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Jeff Janes
On Thu, Jun 28, 2012 at 5:16 AM, 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

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Rob Wultsch
On Thu, Jun 28, 2012 at 8:16 AM, 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

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 th

[HACKERS] Covering Indexes

2012-06-28 Thread David E. Wheeler
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: SEL