Re: Minor TODO list changes - Mailing list pgsql-hackers

In my data warehousing situation, I'd like to be able to specify that
the indexes be as compact as possible (fillfactor = 100%) in order to
hit as few index pages as necessary.

For summary tables there will not be any more inserts or deletions so
the index will not change either.  In that case, there's no point to
leaving any extra room for page-splitting.

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that would
have a fillfactor of 100%.  Then I'd be able to load the table, alter it
to be read-only, then add the appropriate indexes that are automatically
compacted.

Darren

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Thursday, November 04, 2004 12:19 PM
To: Simon Riggs
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Minor TODO list changes

Simon Riggs wrote:
> On Thu, 2004-11-04 at 16:51, Bruce Momjian wrote:
> > OK, I updated all your items.
>
> Thanks
>
> > I removed fillfactor because I thought I was the only one who
> > thought it was valuable and as I remember it was mostly useful for
> > ISAM, which we don't support.  Can you think of a use for a non-100%

> > fillfactor?
> >
>
> I was under the impression the factor was 67% for data loaded on the
> leading-edge of an index, and 50% for other INSERTs.
> (backend/access/nbtree/nbtinsert.c)
>
> Not sure, without checking, what CREATE INDEX and COPY do, but I'm
> guessing it is similar?
>
> Other RDBMS use a higher leading-edge/standard fill factor.
>
> There are situations where I'd want to set it at 90%, or even 100%. If

> I know the update rate is likely to be zero, then I'd like my indexes
> to fit in 10-30% less memory and disk, please.
>
> Or am I missing something?

Oh, good point.  I was thinking of just the leaf pages which I think are
100% filled.

--  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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html