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.
> > - 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.
> 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.
NTT Cyber Space Laboratories
In response to
pgsql-hackers by date
|Next:||From: Hannu Krosing||Date: 2006-02-02 12:26:47|
|Subject: Re: Question about ALTER TABLE SET TABLESPACE locing|
|Previous:||From: Christopher Browne||Date: 2006-02-02 02:32:31|
|Subject: Re: autovacuum|
pgsql-patches by date
|Next:||From: Joachim Wieland||Date: 2006-02-02 09:00:15|
|Subject: TODO-Item: TRUNCATE ... CASCADE|
|Previous:||From: Bruce Momjian||Date: 2006-02-02 04:48:45|
|Subject: Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql|