I just noticed that the OSDL benchmarks for PostgreSQL appear to require
PostgreSQL be compiled with INDEX_MAX_KEYS as 64 rather than the default
of 32.

Any chance the default could be bumped for the 7.4 release?

Does it cause a significant performance issue?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
01 Jul 2003 01:09:52 +0000
Rod Taylor <rbt@rbt.ca>
INDEX_MAX_KEYS to 64?
Rod Taylor <rbt@rbt.ca> writes:
> I just noticed that the OSDL benchmarks for PostgreSQL appear to require
> PostgreSQL be compiled with INDEX_MAX_KEYS as 64 rather than the default
> of 32.

Which one?  I've been testing dbt3 here and not seen that.

> Any chance the default could be bumped for the 7.4 release?

Not without evidence that it doesn't cause performance penalties.
ISTM we have been through this discussion recently, and concluded
that 32 was the place to set it.

			regards, tom lane

Mon, 30 Jun 2003 21:25:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: INDEX_MAX_KEYS to 64? 
On Tue, 2003-07-01 at 01:25, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > I just noticed that the OSDL benchmarks for PostgreSQL appear to require
> > PostgreSQL be compiled with INDEX_MAX_KEYS as 64 rather than the default
> > of 32.
> 
> Which one?  I've been testing dbt3 here and not seen that.

It's in the directions for dbt2, though I haven't found the specific
function(s) that requires it at this point -- can't quite run it on my
system yet for various reasons.

> > Any chance the default could be bumped for the 7.4 release?
> 
> Not without evidence that it doesn't cause performance penalties.
> ISTM we have been through this discussion recently, and concluded
> that 32 was the place to set it.

Yes, I was digging through that discussion.  The test used shows a 4%
difference between 32 and 64.

do 100 times
   select 2+2+2+2+2+2+ ... iterated 9901 times

#define INDEX_MAX_KEYS  16, 32, 64, & 128
#define FUNC_MAX_ARGS  INDEX_MAX_KEYS
make all
make install
initdb

The results were as follows:
INDEX_MAX_KEYS    16    32      64     128
                 -----+-------+------+--------
Time in seconds   48    49      51      55

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
01 Jul 2003 01:40:28 +0000
Rod Taylor <rbt@rbt.ca>
Re: INDEX_MAX_KEYS to 64?
Rod Taylor wrote:
> On Tue, 2003-07-01 at 01:25, Tom Lane wrote:
>>Not without evidence that it doesn't cause performance penalties.
>>ISTM we have been through this discussion recently, and concluded
>>that 32 was the place to set it.
> 
> Yes, I was digging through that discussion.  The test used shows a 4%
> difference between 32 and 64.
> 
> do 100 times
>    select 2+2+2+2+2+2+ ... iterated 9901 times

There was also this, on disk usage - about 25% penalty going from 32 to 
64 (at least for small databases).

Joe Conway wrote:
 > Tom Lane wrote:
 >> Did you happen to make any notes about the disk space occupied by the
 >> database?  One thing I was worried about was the bloat that'd occur
 >> in pg_proc, pg_index, and pg_proc_proname_args_nsp_index.  Aside from
 >> costing disk space, this would indirectly slow things down due to
 >> more I/O to read these tables --- an effect that probably your test
 >> couldn't measure, since it wasn't touching very many entries in any
 >> of those tables.
 >
 >
 > #define INDEX_MAX_KEYS        16
 > #define FUNC_MAX_ARGS        INDEX_MAX_KEYS
 > du -h --max-depth=1 /opt/data/pgsql/data/base/
 > 2.7M    /opt/data/pgsql/data/base/1
 > 2.7M    /opt/data/pgsql/data/base/16862
 > 2.7M    /opt/data/pgsql/data/base/16863
 > 2.7M    /opt/data/pgsql/data/base/16864
 > 3.2M    /opt/data/pgsql/data/base/16865
 > 2.7M    /opt/data/pgsql/data/base/16866
 > 17M     /opt/data/pgsql/data/base
 >
 > #define INDEX_MAX_KEYS        32
 > #define FUNC_MAX_ARGS        INDEX_MAX_KEYS
 >  du -h --max-depth=1 /opt/data/pgsql/data/base/
 > 3.1M    /opt/data/pgsql/data/base/1
 > 3.1M    /opt/data/pgsql/data/base/16862
 > 3.1M    /opt/data/pgsql/data/base/16863
 > 3.1M    /opt/data/pgsql/data/base/16864
 > 3.6M    /opt/data/pgsql/data/base/16865
 > 3.1M    /opt/data/pgsql/data/base/16866
 > 19M     /opt/data/pgsql/data/base
 >
 > #define INDEX_MAX_KEYS        64
 > #define FUNC_MAX_ARGS        INDEX_MAX_KEYS
 > du -h --max-depth=1 /opt/data/pgsql/data/base/
 > 3.9M    /opt/data/pgsql/data/base/1
 > 3.9M    /opt/data/pgsql/data/base/16862
 > 3.9M    /opt/data/pgsql/data/base/16863
 > 3.9M    /opt/data/pgsql/data/base/16864
 > 4.4M    /opt/data/pgsql/data/base/16865
 > 3.9M    /opt/data/pgsql/data/base/16866
 > 24M     /opt/data/pgsql/data/base
 >
 > #define INDEX_MAX_KEYS        128
 > #define FUNC_MAX_ARGS        INDEX_MAX_KEYS
 > du -h --max-depth=1 /opt/data/pgsql/data/base/
 > 5.7M    /opt/data/pgsql/data/base/1
 > 5.7M    /opt/data/pgsql/data/base/16862
 > 5.7M    /opt/data/pgsql/data/base/16863
 > 5.7M    /opt/data/pgsql/data/base/16864
 > 6.3M    /opt/data/pgsql/data/base/16865
 > 5.7M    /opt/data/pgsql/data/base/16866
 > 35M     /opt/data/pgsql/data/base
 >

Here's the thread:
http://archives.postgresql.org/pgsql-hackers/2002-08/msg00258.php

Joe


Joe


Mon, 30 Jun 2003 19:01:52 -0700
Joe Conway <mail@joeconway.com>
Re: INDEX_MAX_KEYS to 64?