Re: Visibility map thoughts - Mailing list pgsql-hackers

Simon Riggs wrote:
> I'm thinking that looking in the visibility map will have a cost also,
> so how will we know whether to bother looking? I'm assuming that we
> won't want to do that lookup in all cases, since it could easily just
> add pathlength and contention in the normal OLTP case.

Yeah, I believe that's true. There's a non-zero cost to return tuples 
from the index as well. Now that we do b-tree scans page at a time, if 
we're going to return data from the index, we need to copy all the 
matching data to backend-private memory when we step to a page.

> Presumably there
> would be a test in the planner to see if an index-only plan was
> possible?

Yes, you can check if all the columns you need are in the index. You 
won't know for sure how much of the visibility map is set until 
execution time, however.

I'm thinking that we're going to have an entry in pg_stats on the 
fraction of set bits in the map, and factor that in the cost estimate of 
index scans.

> ISTM that it would make most sense to do it during BitmapIndex scans.
> Specifically, as an intermediate step between BitmapIndex scan and
> BitmapHeap scan. That would be fairly likely to be a win in most cases
> because the bitmaps should compare easily and the amortised cost per row
> is likely to be very small, even if no heap lookups are avoided.

That only helps with COUNT(*), because until the BitmapHeap scan all you 
have is a bitmap of tuples. You can't return any values from just a 
bitmap. (Seems easy to do, so it's still probably worth doing just for 
COUNT(*), though)


A major design question is how where and how the executor for index-only 
scans should work. The required planner changes will follow from that. 
The simplest solution is to just modify nodeIndexScan.c a little bit to 
check the visibility map before fetching the heap tuple. If the bit in 
the visibility map is set, skip the heap fetch and return the required 
values from the index tuple instead. That would give most of the benefit 
already.

The indexam API needs to be modified as well, because there's currently 
no API to return index tuples from an index.

There's some more fancy stuff we could do if we separated the index scan 
and heap fetch to two different executor nodes. You could for example do 
an index-only scan and join, and check visibility only after the join, 
avoiding unnecessarily accessing the heap for tuples that don't satisfy 
the join condition. In fact we could do that without the visibility map 
as well.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com