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