Re: the big picture for index-only scans

On Sat, Aug 20, 2011 at 4:48 AM, Gokulakannan Somasundaram
<gokul007(at)gmail(dot)com> wrote:
> a) First of all, it(Visibility Map) should have definitely affected the
> scalability of postgres in scenarios where in updates occur during a time
> batch window. May be the increase in speed of vacuums negate that effect.

I think that you have switched gears here and are in this paragraph
talking about the 8.4-era visibility map changes rather than my recent
work. There is zero evidence that those changes caused any
performance problem. I've spent a large chunk of the last four months
looking for scalability problems and I haven't come across any
evidence that this is an issue. If you think it is, let's have a test
case.

> b) Second, currently the index scans  don't touch the visibility map and in
> future they are going to acquire share lock on that. This should increase
> the contention.

Maybe. Of course, we're only talking about cases where the index-only
scan optimization is in use (which is not all of them). And even
then, if bringing in the heap pages would have caused buffer evictions
and the index-only scan avoids that, then you're trading contention
for exclusive locks on the BufFreelistLock and buf mapping locks for
shared-lock contention on the visibility map page. Furthermore, the
latter will (except in very large relations) only need to be
shared-locked and pinned once per scan, whereas you might easily have
a case where each index probe forces replacement of a heap page.

What I am slightly worried about is that our machinery for taking and
releasing buffer pins is going to become a scalability bottleneck at
some point, and certainly very hot pages like root index pages and
visibility map pages could become hot-spots for such contention. But
the experiments I've done so far suggest that there are other more
serious bottlenecks that have to be addressed first. If it does rise
to the list, we'll find a way to fix it, but I think skipping the
index-only scan optimization is going to be a cure worse than the
disease.

> c) Your statement : "The contention on the VM buffer would be just as bad
> whether you hold the heap page lock at the same time or not."
> I am talking about the contention time frame of the heap page. It will be
> increased Consider my statement in conjunction with the scenario 2.

Sure, but here again, what is your evidence that this actually
matters? It's not increasing by very much.

> 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.

> You might definitely see some performance improvement, if you are testing
> this in anything less than 4 cores. Bump up the core count and processor
> count and check whether this affects the load-throughput curve.

I'm fairly certain I already did that experiment, on a 32-core
machine, but since the patch you're worried about went in two months
ago, I'm a little fuzzy on the details. Maybe you should test it out
yourself....

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