Re: new index type with clustering in mind.

> in 9.4, GIN indexes are pretty close to this already

Do I understand correctly that BRIN indexes will be even closer to this?

Kindest regards
Jack

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 24 May 2014 22:46
To: Martijn van Oosterhout
Cc: Jack Douglas; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] new index type with clustering in mind.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Sat, May 24, 2014 at 05:58:37PM +0100, Jack Douglas wrote:
>> Would the following be practical to implement:
>> A btree-like index type that points to *pages* rather than individual
rows.

> It's an interesting idea, but, how can you *ever* delete index entries?
> I.e. is there a way to maintain the index without rebuilding it
> regularly?

The discussions at PGCon pointed out that with the posting-list compression
logic added in 9.4, GIN indexes are pretty close to this already. Multiple
items on the same heap page will typically only take one byte of index space
per item; but there is an identifiable entry, so you don't get into these
questions of when VACUUM should remove entries, and it's not lossy so you're
not forced to pay the overhead of rechecking every entry on the linked-to
page.

Not to say that 9.4 GIN is necessarily the last word on the subject, but it
would be worth testing it out before deciding that we need something better.
(beta1 is out. It needs testing. Hint hint.)

regards, tom lane