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

Re: TODO-Item: B-tree fillfactor control

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: ITAGAKI Takahiro <itagaki(dot)takahiro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: TODO-Item: B-tree fillfactor control
Date: 2006-02-02 17:47:43
Message-ID: 200602021747.k12Hlha29377@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
ITAGAKI Takahiro wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> 
> > > - Is fillfactor useful for hash and gist indexes?
> > >     I think hash does not need it, but gist might need it.
> > 
> > Not sure.  We don't know what type of index a GIST will be so we have no
> > way of knowing.  I am thinking we can implement just btree now and the
> > GIST folks can add it later if they want.  My guess is that each GIST is
> > going to behave differently for different fill-factors, so if allow it
> > to be set for GIST, GIST developers can pull the value if they want.
> 
> My understanding about hash was wrong. It uses fill factor of 75%, which is
> hard-coded. On the other hand, GIST has no ability to control fill factor
> currently. I'm trying to add fill factors to hash and gist, so I'll ask
> index developers to review a patch in the future.

OK.

> > > - Is it appropriate to use GUC variables to control fillfactors?
> > >     Is it better to extend CREATE INDEX / REINDEX grammar?
> > 
> > I think it has to be part of CREATE INDEX and ALTER INDEX.
> 
> SQL standard has no regulation for indexes, so I refered to other databases.
>   - Oracle and DB2 : CREATE INDEX index ON table (...) PCTFREE 30;
>   - MS SQL Server  : CREATE INDEX index ON table (...) WITH FILLFACTOR = 70;
> 
> PCTFREE seems to be common, so I'll extend DDL to use PCTFREE syntax.
> The following two syntaxes will be able to be used. 
>   1. SET btree_free_percent = 30;
>      CREATE INDEX index ON table (...);
>      SET btree_free_percent = 10; -- revert
>   2. CREATE INDEX index ON table (...) PCTFREE 30;
> 
> 1 would be useful for a compatibe pg_dump format, per suggestion from Tom.

I personally like FILLFACTOR, but I understand the desire to match
Oracle.  PCTFREE seems too abreviated for me, but it would match the GUC
better, so maybe it is the best.

> > Is there a use for separate node and leaf settings?
> 
> We should use different settings for leaf and node, but it may confuse users.
> So I'll simplify the setting as follows:
>         node_free_percent = Min(30%, 3 * leaf_free_percent)
> When leaf_free_percent is 10%, node_free_percent is 30%. They are the same
> values of the current implementation.

Yes, I think that is ideal.

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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-02-02 17:48:37
Subject: Re: Some platform-specific MemSet research
Previous:From: Rocco AltierDate: 2006-02-02 17:42:17
Subject: Re: Some platform-specific MemSet research

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-02-02 17:48:37
Subject: Re: Some platform-specific MemSet research
Previous:From: Rocco AltierDate: 2006-02-02 17:42:17
Subject: Re: Some platform-specific MemSet research

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