Re: Indices for select count(*)?

"Jim C. Nasby"  writes:

> I didn't think the method of adding the imperfect known_visible bit to
> the indexes had that much overhead, but it's been a while since those
> discussions took place. I do recall some issue being raised that will be
> very difficult to solve (though again I don't remember the details now).

I doubt very much any visibility information will ever make it into the
indexes. The cost to update it in all the indexes terrible, and when would
that update even happen?

The proposal that had the most going for it was to maintain a bit in the FSM
or something like it that was your "known visible" bit. That would speed up
index scans and vacuums too. It would largely solve the problem with vacuuming
large tables that have mostly untouched pages.

The reason Oracle gets away with this is because they use optimistic MVCC
where the new record replaces the old one entirely. They keep the old records
in a separate space entirely. You pay the costs elsewhere instead. In Oracle
every update requires updating the rollback segment too, and if you have a
very busy table each record can cause you a second (or even third or fourth)
read in the rollback segment. And you pay these costs on *all* scans.

-- 
greg