Skip site navigation (1) Skip section navigation (2)

Re: vacuum, performance, and MVCC

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Csaba Nagy <nagy(at)ecircle-ag(dot)com>,Hannu Krosing <hannu(at)skype(dot)net>,Mark Woodward <pgsql(at)mohawksoft(dot)com>,"Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>,Christopher Browne <cbbrowne(at)acm(dot)org>
Subject: Re: vacuum, performance, and MVCC
Date: 2006-06-27 16:18:52
Message-ID: 20060627161852.GH44573@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Jun 26, 2006 at 11:08:24PM -0400, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > On Mon, Jun 26, 2006 at 02:32:59PM -0400, Bruce Momjian wrote:
> > > 
> > > It is certainly possible to do what you are suggesting, that is have two
> > > index entries point to same chain head, and have the index access
> > > routines figure out if the index qualifications still hold, but that
> > > seems like a lot of overhead.
> > > 
> > > Also, once there is only one visible row in the chain, removing old
> > > index entries seems quite complex because you have to have vacuum keep
> > > the qualifications of each row to figure out which index tuple is the
> > > valid one (seems messy).
> >  
> > Perhaps my point got lost... in the case where no index keys change
> > during an update, SITC seems superior in every way to my proposal. My
> > idea (let's call it Index Tuple Page Consolidation, ITPC) would be
> > beneficial to UPDATEs that modify one or more index keys but still put
> > the tuple on the same page. Where SITC would be most useful for tables
> > that have a very heavy update rate and very few indexes, ITPC would
> > benefit tables that have more indexes on them; where presumably it's
> > much more likely for UPDATEs to change at least one index key (which
> > means SITC goes out the window, if I understand it correctly). If I'm
> > missing something and SITC can in fact deal with some index keys
> > changing during an UPDATE, then I see no reason for ITPC.
> 
> I understood what you had said.  The question is whether we want to get
> that complex with this feature, and if there are enough use cases
> (UPDATE with index keys changing) to warrant it.

Ideas on how to test a table to see how many tuples would fit this
criteria?

Or we could just shelve ITPC as a possibility in the future, after we
see how much the limitations of SITC hurt.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-06-27 16:21:40
Subject: Re: vacuum, performance, and MVCC
Previous:From: Bruce MomjianDate: 2006-06-27 16:18:23
Subject: Re: vacuum, performance, and MVCC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group