ITAGAKI Takahiro wrote:
> Hi Hackers,
>
> I'm trying the following TODO item:
> [Indexes]
> - Add fillfactor to control reserved free space during index creation
>
> I have already made an patch and it seemed to work well.

Great.

> And now, I need advice on some issues.
>
> - 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.

Is there a use for separate node and leaf settings?

This patch needs documentation, and if we have separate items, we are
going to have to explain when to use node or leaf.

> - Should indexes remember their fillfactors when they are created?
> The last fillfactors will be used on next reindex.

They should remember, for sure, and REINDEX should use it. It think
this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
functionality. It will need to be dumped as well by pg_dump. If you
need help with any of this, let me know.

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

--
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 6: explain analyze is your friend

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> - Should indexes remember their fillfactors when they are created?
>> The last fillfactors will be used on next reindex.

> They should remember, for sure, and REINDEX should use it. It think
> this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
> functionality. It will need to be dumped as well by pg_dump.

If you want it to be dumped by pg_dump (which is debatable IMHO) then
it MUST NOT be a syntax extension, it has to be driven by a GUC
variable, else we have compatibility problems with the dumps. We just
went through this with WITH/WITHOUT OIDS.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> - Should indexes remember their fillfactors when they are created?
> >> The last fillfactors will be used on next reindex.
>
> > They should remember, for sure, and REINDEX should use it. It think
> > this is similar to the ALTER TABLE ALTER [ COLUMN ] ... SET STATISTICS
> > functionality. It will need to be dumped as well by pg_dump.
>
> If you want it to be dumped by pg_dump (which is debatable IMHO) then
> it MUST NOT be a syntax extension, it has to be driven by a GUC
> variable, else we have compatibility problems with the dumps. We just
> went through this with WITH/WITHOUT OIDS.

OK, so we are going to issue a GUC to set the fill factor in pg_dump,
but still have a fillfactor syntax for use by users? That is how we do
WITH/WITHOUT OIDS.

--
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 2: Don't 'kill -9' the postmaster

> If you want it to be dumped by pg_dump (which is debatable IMHO) then
> it MUST NOT be a syntax extension, it has to be driven by a GUC
> variable, else we have compatibility problems with the dumps. We just
> went through this with WITH/WITHOUT OIDS.

Compatibility problems? CREATE INDEX isn't an SQL standard command is it?

Chris


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Compatibility problems? CREATE INDEX isn't an SQL standard command is it?

No, but it'll cause unnecessary cross-version compatibility issues for
us.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > Compatibility problems? CREATE INDEX isn't an SQL standard command is it?
>
> No, but it'll cause unnecessary cross-version compatibility issues for
> us.

It is true it isn't SQL standard, but I think our CREATE INDEX syntax
matches many vendor's syntax in most cases.

--
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 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bruce Momjian <pgman@candle.pha.pa.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.

---
ITAGAKI Takahiro
NTT Cyber Space Laboratories



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

ITAGAKI Takahiro wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@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 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly