Re: the big picture for index-only scans

On Sat, Aug 20, 2011 at 4:48 PM, Gokulakannan Somasundaram <
gokul007(at)gmail(dot)com> wrote:

>
> The above could already happen in 8.4, where the visibility map was
>> introduced. 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 have not heard any
>> complaints of contention on VM buffers.
>>
>> --
>> Heikki Linnakangas
>>
>>
>> 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.
> 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.
> 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.
> 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.
>
> 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.
>
>
By your argument, we can say that no-one will create a index with a function
like random(), time(), date(), broken operators etc. Its hard to imagine a
index in which a a user will only want to insert and never select on it.
Even C++ provides std::map infrastructure for objects, where the user owns
the responsibility of writing the operator< correctly. Other databases do
the same. Even going one step ahead, we are already going back to such
indexes, if there is unique constraint/ referential integrity constraints.
But with all such caveats, it was decided we should not allow covering
indexes, as they require going back to the index for updates/deletes.