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

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