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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]