Re: the big picture for index-only scans

On Sun, Aug 21, 2011 at 12:10 AM, Gokulakannan Somasundaram
<gokul007(at)gmail(dot)com> wrote:
> Consider the TPC-C benchmark. Currently on a four core box, Oracle does
> 290000 transactions per minute. Say we are doing something around half of
> this. So a page should get quickly filled. If a vacuum runs and the
> transactions are not touched by the MakePayment transaction, then it will be
> marked as AllVisible. When the MakePayment transaction updates, it should
> clear that bit. If we test it out, with too little warehouses, we may not
> see the effect. Of Course the PGPROC infrastructure for generating
> transaction ids might be the biggest culprit, but if you ignore that this
> should be visible.

Actual testing does not appear to show a bottleneck in either of these areas.

>> Maybe.  Of course, we're only talking about cases where the index-only
>> scan optimization is in use (which is not all of them).
>
> But are you saying that, the optimization of looking at visibility map will
> happen only for Index-only scans and will not use the visibility map
> infrastructure for the normal index scans? That's definitely a good idea and
> improvement.

Well, yes. And not only that, but the index-only scans patch has been
posted on this very mailing list, along with detailed submission
notes. So you could go read, or try it, before jumping to the
conclusion that it doesn't work.

>>> d) In addition, currently there is no WAL Logging, while the bit is
>>> cleared,
>>> which would not be the case in future and hence the exclusive lock held
>>> on
>>> the visibility map is going to be held for a longer time.
>
>> This is false and has been false since the visibility map was first
>> implemented.
>
> I can't understand this. If you are not doing this, then it would cause
> consistency issues. Are you saying, we have a crash safe visibility map, but
> you don't follow "log the change before changing the contents"/ WAL
> principle. If so, please explain in detail. If you are doing it in the
> normal way, then you should be logging the changes before making the changes
> to the buffer and during that timeframe, you should be holding the lock on
> the buffer. Heikki specifically pointed out, that you have brought in the
> WAL Logging of visibility map, within the critical section.
>
> Heikki's comments, i am quoting:
>  "I believe Robert's changes to make the visibility map crash-safe covers
> that. Clearing the bit in the visibility map now happens within the same
> critical section as clearing the flag on the heap page and writing the WAL
> record."
>
> I would be able to respond to your other statements, once we are clear here.

There are extensive comments on this in visibilitymap.c and, in
heapam.c, heap_xlog_visible().

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company