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