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@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,
Pennsylvania19073