Re: plans for bitmap indexes? - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: plans for bitmap indexes?
Date
Msg-id 1098869900.2550.8.camel@fuji.krosing.net
Whole thread Raw
In response to Re: plans for bitmap indexes?  (Andre Maasikas <andre@abs.ee>)
Responses Re: plans for bitmap indexes?
List pgsql-hackers
On K, 2004-10-27 at 00:58, Andre Maasikas wrote:
> Hannu Krosing wrote:
> > the per-page clustering would make sure that all the tuples would be on
> > 1 (or on a few) pages.
> 
> I understand that You can cluster on one column, but how do you do it for
> indexes on other columns?

Thanks to PostgreSQL's MVCC each update inserts a complete new tuple -
you just have to insert in the right page.

so if I change foo=1 to foo=2 on a tuple that has bar=2 and baz=3 then
the updated tuple will go to a page for which foo=2, bar=2 and baz=3.

if no such page has enough free space left (found by anding bitmaps for
foo=2, bar=2 and baz=3 and FSM) then a new page is inserted and the
three corresponding indexes are updated to include that page.

> BTW, lossy variants also lose count(), group by only from index

PostgreSQL has never been able to do these from index only, as the
visibility info is stored in the main relation, and not in index.

Someone brings up adding visibility info to index about once in 6 months
and is referred to previous discussions as to why it is a bad idea. The
only thing that as been added to index is a bit telling if a tuple is
definitely invisible (i.e. older than any pending transaction) which is
updated when such tuple is accessed using this index.


> > and what comes to updating the index, you have to do it only once per
> > 100 pages ;)
> 
> Sorry, how does that work, if I update foo = 'bar'->'baz' - I can flip 
> the 'baz' bit
> on right away but I have to check every other row to see
> if I can turn the 'bar' bit off

You don't touch indexes, instead you select the right page for new
tuple. The only times you touch indexes is when you insert a new page
(or when the page becomes empty during vacuum)

----------------
Hannu




pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Should bgwriter log checkpoint start/end?
Next
From: ohp@pyrenet.fr
Date:
Subject: Re: Unixware 714 pthreads