Performance penalty of visibility info in indexes?

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Ühel kenal päeval, P, 2007-02-04 kell 22:23, kirjutas Jim Nasby:

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.

Not always. If we do in-page update and keep the unchanged index entry
pointing to the first tuple inside the page, then the indexes visibility
info would still be valid for that tuple and also right for that field.

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.

A large read-mostly table would also benefit from separating the
visibility info out to a compressed visibility heap.

Of course this would have to be optional; there's lots of cases where
you wouldn't want the added index size.

Of course. All alternative ways of storing MVCC info should be optional
and user-selectable so DBA can test and select the most suitable one for
each usecase.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

On Thu, Feb 01, 2007 at 11:57:41PM -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 thought the main problem was locking. If you change the visibility of
an existing row, you have to update the index in a way that won't kill
concurrant scans, either by returning the row twice, or skipping it.

I think it hinges on what exactly falls under "visibility info". Maybe
with the page-at-a-time index scans, the problem is easier now.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/