Thread: INDEX_MAX_KEYS to 64?

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

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

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

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?
Onething I was worried about was the bloat that'd occur>> in pg_proc, pg_index, and pg_proc_proname_args_nsp_index.
Asidefrom>> costing disk space, this would indirectly slow things down due to>> more I/O to read these tables --- an
effectthat 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