* 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
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 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
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
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
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 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
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
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
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
"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.
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
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
> -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 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
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
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
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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,
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
36 matches
Mail list logo
