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

Re: Resurrecting per-page cleaner for btree

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,pgsql-hackers(at)postgresql(dot)org, teramoto(dot)junji(at)lab(dot)ntt(dot)co(dot)jp
Subject: Re: Resurrecting per-page cleaner for btree
Date: 2006-07-27 09:46:07
Message-ID: 20060727182953.52E7.ITAGAKI.TAKAHIRO@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> The attached patch requires the new row to fit, and 10% to be free on
> the page.  Would someone test that?


This is another solution for the same purpose. We can avoid to call
PageIndexMultiDelete() to remove only one tuple. _bt_split() becomes
to ignore tuples with LP_DELETE because such tuples are passed now.
The cleaner threshold (currently 2) might have to be more tweaked.

Can this change resolve your performance problem?


diff -cpr pgsql-orig/src/backend/access/nbtree/nbtinsert.c pgsql/src/backend/access/nbtree/nbtinsert.c
*** pgsql-orig/src/backend/access/nbtree/nbtinsert.c	Wed Jul 26 11:15:20 2006
--- pgsql/src/backend/access/nbtree/nbtinsert.c	Thu Jul 27 18:27:25 2006
*************** _bt_split(Relation rel, Buffer buf, Offs
*** 788,793 ****
--- 788,798 ----
  	for (i = P_FIRSTDATAKEY(oopaque); i <= maxoff; i = OffsetNumberNext(i))
  	{
  		itemid = PageGetItemId(origpage, i);
+ 
+ 		/* We can ignore items with LP_DELETE. */
+  		if (ItemIdDeleted(itemid))
+ 			continue;
+ 
  		itemsz = ItemIdGetLength(itemid);
  		item = (IndexTuple) PageGetItem(origpage, itemid);
  
*************** _bt_vacuum_one_page(Relation rel, Buffer
*** 1707,1713 ****
  			deletable[ndeletable++] = offnum;
  	}
  
! 	if (ndeletable > 0)
  		_bt_delitems(rel, buffer, deletable, ndeletable);
  	/*
  	 * Note: if we didn't find any LP_DELETE items, then the page's
--- 1712,1723 ----
  			deletable[ndeletable++] = offnum;
  	}
  
! 	/*
! 	 * We delete items if there are at least 2 deletable index tuples
! 	 * because scanning the page over and over again to get just a little
! 	 * free space is inefficient.
! 	 */
! 	if (ndeletable >= 2)
  		_bt_delitems(rel, buffer, deletable, ndeletable);
  	/*
  	 * Note: if we didn't find any LP_DELETE items, then the page's


Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-07-27 12:25:17
Subject: Re: GUC with units, details
Previous:From: Susanne EbrechtDate: 2006-07-27 09:36:35
Subject: Re: extension for sql update

pgsql-patches by date

Next:From: Tom LaneDate: 2006-07-27 12:30:21
Subject: Re: extension for sql update
Previous:From: Susanne EbrechtDate: 2006-07-27 09:36:35
Subject: Re: extension for sql update

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