Re: Performance penalty of visibility info in indexes?

On Feb 2, 2007, at 1:41 PM, Simon Riggs wrote:
> On Thu, 2007-02-01 at 23:57 -0600, Jim Nasby wrote:
>> Has anyone actually measured the performance overhead of storing
>> visibility info in indexes? I know the space overhead sounds
>> daunting, but even if it doubled the size of the index in many cases
>> that'd still be a huge win over having to scan the heap as well as
>> the index (esp. for things like count(*)). There would also be
>> overhead from having to update the old index tuple, but for the case
>> of updates you're likely to need that page for the new index tuple
>> anyway.
>>
>> I know this wouldn't work for all cases, but ISTM there are many
>> cases where it would be a win.
>
> It would prevent any optimization that sought to avoid inserting rows
> into the index each time we perform an UPDATE. Improving UPDATE
> performance seems more important than improving count(*), IMHO.

That depends on what you're doing; a large read-mostly table would
likely see a lot of benefit from being able to do covering index scans.

Of course this would have to be optional; there's lots of cases where
you wouldn't want the added index size.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)