Re: 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(at)interwiz(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073