Thread: ToDo list update for BRIN indexes
Is there anything in the below section which has been been implemented or rendered irrelevant by BRIN indexes?
Cheers,
Jeff
On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> 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
On 21 December 2015 at 12:54, Robert Haas <robertmhaas@gmail.com> wrote:
--
On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> 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 Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 21 December 2015 at 12:54, Robert Haas <robertmhaas@gmail.com> wrote: >> >> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> 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
On 21 December 2015 at 14:38, Robert Haas <robertmhaas@gmail.com> wrote:
--
On Mon, Dec 21, 2015 at 8:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 21 December 2015 at 12:54, Robert Haas <robertmhaas@gmail.com> wrote:
>>
>> On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> 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 Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Dec 21, 2015 at 07:54:43AM -0500, Robert Haas wrote: > On Thu, Jul 9, 2015 at 4:49 PM, Jeff Janes <jeff.janes@gmail.com> 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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +