Re: [HACKERS] ToDo list update for BRIN indexes

[Bruce Momjian]

On Mon, Dec 21, 2015 at 07:54:43AM -0500, Robert Haas wrote:
> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes  wrote:
> > Is there anything in the below section which has been been implemented or
> > rendered irrelevant by BRIN indexes?
> >
> > https://wiki.postgresql.org/wiki/Todo#Indexes
> >
> > "Consider smaller indexes that record a range of values per heap page,
> > rather than having one index entry for every heap row"
> 
> [ catching up on old threads ]
> 
> BRIN is exactly this, isn't it?  Well, moreso: it's a range of values
> for a range of heap pages.

FYI, that TODO entry was removed since July 9, probably as part of my
9.5 cleanup.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo list update for BRIN indexes

[Simon Riggs]

On 21 December 2015 at 14:38, Robert Haas  wrote:

> On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs 
> wrote:
> > On 21 December 2015 at 12:54, Robert Haas  wrote:
> >>
> >> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes 
> wrote:
> >> > Is there anything in the below section which has been been implemented
> >> > or
> >> > rendered irrelevant by BRIN indexes?
> >> >
> >> > https://wiki.postgresql.org/wiki/Todo#Indexes
> >> >
> >> > "Consider smaller indexes that record a range of values per heap page,
> >> > rather than having one index entry for every heap row"
> >>
> >> [ catching up on old threads ]
> >>
> >> BRIN is exactly this, isn't it?  Well, moreso: it's a range of values
> >> for a range of heap pages.
> >
> > It's close, but not the same.
> >
> > BRIN is a summary index and so could never support uniqueness.
>
> Hmm, but that Todo wording seems to suggest a summary index, so I
> don't think that proposal would support uniqueness either.


Probably garbled slightly.


> > It's also possible to have an index type that has a precise TID entry,
> yet a
> > more compact format, which would then allow unique values. This would be
> > similar to the way SQLServer compresses primary key indexes.
>
> True.  But would that require a new index type, or would we do that
> just by optimizing btree?


Heikki worked on Grouped Item Tuple indexes about 8 years ago doing exactly
that. They gave about 30% performance advantage at the time, when used with
HOT, which was the original driver for that feature.

BRIN and GIT approaches degrade under heavy non-HOT updates.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] ToDo list update for BRIN indexes

[Robert Haas]

On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs  wrote:
> On 21 December 2015 at 12:54, Robert Haas  wrote:
>>
>> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes  wrote:
>> > Is there anything in the below section which has been been implemented
>> > or
>> > rendered irrelevant by BRIN indexes?
>> >
>> > https://wiki.postgresql.org/wiki/Todo#Indexes
>> >
>> > "Consider smaller indexes that record a range of values per heap page,
>> > rather than having one index entry for every heap row"
>>
>> [ catching up on old threads ]
>>
>> BRIN is exactly this, isn't it?  Well, moreso: it's a range of values
>> for a range of heap pages.
>
> It's close, but not the same.
>
> BRIN is a summary index and so could never support uniqueness.

Hmm, but that Todo wording seems to suggest a summary index, so I
don't think that proposal would support uniqueness either.

> It's also possible to have an index type that has a precise TID entry, yet a
> more compact format, which would then allow unique values. This would be
> similar to the way SQLServer compresses primary key indexes.

True.  But would that require a new index type, or would we do that
just by optimizing btree?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo list update for BRIN indexes

[Simon Riggs]

On 21 December 2015 at 12:54, Robert Haas  wrote:

> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes  wrote:
> > Is there anything in the below section which has been been implemented or
> > rendered irrelevant by BRIN indexes?
> >
> > https://wiki.postgresql.org/wiki/Todo#Indexes
> >
> > "Consider smaller indexes that record a range of values per heap page,
> > rather than having one index entry for every heap row"
>
> [ catching up on old threads ]
>
> BRIN is exactly this, isn't it?  Well, moreso: it's a range of values
> for a range of heap pages.
>

It's close, but not the same.

BRIN is a summary index and so could never support uniqueness.

It's also possible to have an index type that has a precise TID entry, yet
a more compact format, which would then allow unique values. This would be
similar to the way SQLServer compresses primary key indexes.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] ToDo list update for BRIN indexes

[Robert Haas]

On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes  wrote:
> Is there anything in the below section which has been been implemented or
> rendered irrelevant by BRIN indexes?
>
> https://wiki.postgresql.org/wiki/Todo#Indexes
>
> "Consider smaller indexes that record a range of values per heap page,
> rather than having one index entry for every heap row"

[ catching up on old threads ]

BRIN is exactly this, isn't it?  Well, moreso: it's a range of values
for a range of heap pages.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ToDo list update for BRIN indexes

[Jeff Janes]

Is there anything in the below section which has been been implemented or
rendered irrelevant by BRIN indexes?

https://wiki.postgresql.org/wiki/Todo#Indexes

"Consider smaller indexes that record a range of values per heap page,
rather than having one index entry for every heap row"

Cheers,

Jeff