Changes improve the performance of INSERT and UPDATE

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Hiroki Kataoka wrote:

Hi all,

This small patch improves the performance of INSERT and UPDATE. By my
machine, these changes raised the performance about 5%~10% in pgbench.
Please take a benchmark in a reliable environment. Since I may forget
some required changes, I specify that this patch is experimental. But
note that all regression tests have passed.

Thanks,

--
Hiroki Kataoka <kataoka@interwiz.jp>

diff -ru postgresql-cvs.orig/src/backend/storage/page/bufpage.c postgresql-cvs/src/backend/storage/page/bufpage.c
--- postgresql-cvs.orig/src/backend/storage/page/bufpage.c	2005-06-15 16:53:00.000000000 +0900
+++ postgresql-cvs/src/backend/storage/page/bufpage.c	2005-07-14 07:41:11.000000000 +0900
@@ -42,6 +42,7 @@
p->pd_lower = SizeOfPageHeaderData;
p->pd_upper = pageSize - specialSize;
p->pd_special = pageSize - specialSize;
+	p->pd_insoff = FirstOffsetNumber;
PageSetPageSizeAndVersion(page, pageSize, PG_PAGE_LAYOUT_VERSION);
}

@@ -100,7 +101,7 @@
*	If offsetNumber is valid and <= current max offset in the page,
*	insert item into the array at that position by shuffling ItemId's
*	down to make room.
- *	If offsetNumber is not valid, then assign one by finding the first
+ *	If offsetNumber is not valid, then assign one by finding the next
*	one that is both unused and deallocated.
*
*	!!! EREPORT(ERROR) IS DISALLOWED HERE !!!
@@ -120,6 +121,7 @@
OffsetNumber limit;
bool		needshuffle = false;
bool		overwritemode = (flags & OverwritePageMode) != 0;
+	bool		findunusedlinp = false;

flags &= ~OverwritePageMode;

@@ -165,9 +167,15 @@
}
else
{
-		/* offsetNumber was not passed in, so find a free slot */
-		/* look for "recyclable" (unused & deallocated) ItemId */
-		for (offsetNumber = 1; offsetNumber < limit; offsetNumber++)
+		/* offsetNumber was not passed in, try to use pd_insoff */
+		if (OffsetNumberIsValid(phdr->pd_insoff))
+			offsetNumber = phdr->pd_insoff;
+		else
+			offsetNumber = FirstOffsetNumber;
+
+		/* so find a free slot look for "recyclable" (unused & deallocated) */
+		/* ItemId */
+		for (; offsetNumber < limit; offsetNumber++)
{
itemId = PageGetItemId(phdr, offsetNumber);
if (((itemId->lp_flags & LP_USED) == 0) &&
@@ -175,6 +183,8 @@
break;
}
/* if no free slot, we'll put it at limit (1st open slot) */
+
+		findunusedlinp = true;
}

if (offsetNumber > limit)
@@ -222,6 +232,8 @@
/* adjust page header */
phdr->pd_lower = (LocationIndex) lower;
phdr->pd_upper = (LocationIndex) upper;
+	if (findunusedlinp)
+		phdr->pd_insoff = OffsetNumberNext(offsetNumber);

return offsetNumber;
}
@@ -345,8 +357,13 @@
lp->lp_flags &= ~(LP_USED | LP_DELETE);
if (lp->lp_flags & LP_USED)
nused++;
-		else if (unused)
-			unused[i - nused] = (OffsetNumber) i;
+		else
+		{
+			if (i - nused == 0)	/* check if it's first unused line pointer */
+				((PageHeader) page)->pd_insoff = (OffsetNumber) i + 1;
+			if (unused)
+				unused[i - nused] = (OffsetNumber) i;
+		}
}

if (nused == 0)
diff -ru postgresql-cvs.orig/src/include/storage/bufpage.h postgresql-cvs/src/include/storage/bufpage.h
--- postgresql-cvs.orig/src/include/storage/bufpage.h	2005-04-29 06:47:18.000000000 +0900
+++ postgresql-cvs/src/include/storage/bufpage.h	2005-07-14 06:57:03.000000000 +0900
@@ -125,6 +125,7 @@
LocationIndex pd_lower;		/* offset to start of free space */
LocationIndex pd_upper;		/* offset to end of free space */
LocationIndex pd_special;	/* offset to start of special space */
+	OffsetNumber pd_insoff;		/* offset number for next new item */
uint16		pd_pagesize_version;
ItemIdData	pd_linp[1];		/* beginning of line pointer array */
} PageHeaderData;

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073