This is a benchmark of several versions of Postgresql :
All were compiled from source using gcc 3.3.4. With the exception of 7.3.7, all were built with the the following autoconf options:
$ ./configure --prefix=path --with-openssl --with-pam
7.3.7 required an (additional) modification - amending CFLAGS in src/Makefile.global to get -O2 compilation (enabled by default for the rest).
Tuning parameters were set as detailed in the documentation for Benchw version 1.1 - i.e. 100M of buffers and 20M sort per process.
The default suggested indexes were used.
The benchmark was performed on 24/08/2004 by markir@paradise.net.nz
| Mobo/CPU/Memory | Tyan Tiger 133 2xPIII 700Mhz 2G |
| IO | 2x40G 7200 RPM ATA-133 PROMISE TX2000 RAID 0 |
| OS | FreeBSD 4.10 |
Both sets of results were for size = 1 (the default)
This first set of results consisted of a complete run of load, index and query. Note that some caching of data from the load and index stages effected the queries.
| Test | 8.0 | 7.4 | 7.3 | 7.2 |
|---|---|---|---|---|
| Load | 401s | 409s | 381s | 346s |
| Index | 643s | 688s | 632s | 843s |
| Query0 | 5s | 5s | 9s | 10s |
| Query1 | 3s | 3s | 7s | 9s |
| Query2 | 39s | 36s | 61s | 55s |
| Query3 | 36s | 35s | 60s | 57s |
| Query4 | 49s | 47s | 122s | 133s |
This second run consisted of queries only, after a system restart to forceably clear all caches. Therefore caching effect derived purely from any repeated scanning of data visited in previous queries. Note that this was particularly noticeable for the second query - as it retrieved the same data as the first.
| Test | 8.0 | 7.4 | 7.3 | 7.2 |
|---|---|---|---|---|
| Query0 | 25s | 28s | 33s | 38s |
| Query1 | 3s | 3s | 8s | 9s |
| Query2 | 52s | 49s | 78s | 72s |
| Query3 | 36s | 35s | 62s | 59s |
| Query4 | 49s | 48s | 124s | 135s |
The schema used is reproduced below, it is a simple 'star' with 1 'fact' and 3 'dimensions', one of which is 'timelike'.
--
-- benchw schema :
--
-- timelike dimension
-- cardinality = 10000
CREATE TABLE dim0 (
d0key INTEGER NOT NULL,
ddate DATE NOT NULL,
dyr INTEGER NOT NULL,
dmth INTEGER NOT NULL,
dday INTEGER NOT NULL
);
-- dimension
-- cardinality = 10000
CREATE TABLE dim1 (
d1key INTEGER NOT NULL,
dat VARCHAR(100) NOT NULL,
dattyp VARCHAR(20) NOT NULL,
dfill VARCHAR(100) NOT NULL
);
-- dimension
-- cardinality = 10000
CREATE TABLE dim2 (
d2key INTEGER NOT NULL,
dat VARCHAR(100) NOT NULL,
dattyp VARCHAR(20) NOT NULL,
dfill VARCHAR(100) NOT NULL
);
-- fact
-- cardinality = 10000000
CREATE TABLE fact0 (
d0key INTEGER NOT NULL,
d1key INTEGER NOT NULL,
d2key INTEGER NOT NULL,
fval INTEGER NOT NULL,
ffill VARCHAR(100) NOT NULL
);
--
-- benchw (suggested) indexes :
--
CREATE UNIQUE INDEX dim0_d0key ON dim0(d0key)
;
CREATE UNIQUE INDEX dim1_d1key ON dim1(d1key)
;
CREATE UNIQUE INDEX dim2_d2key ON dim2(d2key)
;
CREATE INDEX fact0_d0key ON fact0(d0key)
;
CREATE INDEX fact0_d1key ON fact0(d1key)
;
CREATE INDEX fact0_d2key ON fact0(d2key)
;
The queries are reproduced below.
--
-- benchw query : generated by querygen
-- : query type 0
--
SELECT
d0.dmth,
count(f.fval )
FROM
dim0 AS d0,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d0.ddate BETWEEN '2010-01-01' AND '2010-12-28'
GROUP BY
d0.dmth
;
--
-- benchw query : generated by querygen
-- : query type 1
--
SELECT
d0.dmth,
count(f.fval )
FROM
dim0 AS d0,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d0.dyr = 2010
GROUP BY
d0.dmth
;
--
-- benchw query : generated by querygen
-- : query type 2
--
SELECT
d0.dmth,
d1.dat,
count(f.fval )
FROM
dim0 AS d0,
dim1 AS d1,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d1.d1key = f.d1key
AND d0.dyr BETWEEN 2010 AND 2015
AND d1.dattyp BETWEEN '10th measure type' AND '14th measure type'
GROUP BY
d0.dmth,
d1.dat
;
--
-- benchw query : generated by querygen
-- : query type 3
--
SELECT
d0.dmth,
d1.dat,
d2.dat,
count(f.fval )
FROM
dim0 AS d0,
dim1 AS d1,
dim2 AS d2,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d1.d1key = f.d1key
AND d2.d2key = f.d2key
AND d0.dyr BETWEEN 2010 AND 2015
AND d1.dattyp BETWEEN '10th measure type' AND '14th measure type'
AND d2.dattyp BETWEEN '1th measure type' AND '4th measure type'
GROUP BY
d0.dmth,
d1.dat,
d2.dat
;
--
-- benchw query : generated by querygen
-- : query type 4
--
SELECT
d0.dyr,
count(f.fval )
FROM
dim0 AS d0,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d0.dyr < 2010
GROUP BY
d0.dyr
;
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=286953.94..286953.94 rows=1 width=8)
-> Nested Loop (cost=0.00..285268.93 rows=337002 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=337 width=8)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate <= '2010-12-28'::date))
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..833.07 rows=1022 width=8)
Index Cond: ("outer".d0key = f.d0key)
(6 rows)
?column?
----------
Query 1
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=284376.40..284376.40 rows=1 width=8)
-> Nested Loop (cost=0.00..282706.39 rows=334002 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..194.00 rows=334 width=8)
Filter: (dyr = 2010)
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..833.07 rows=1022 width=8)
Index Cond: ("outer".d0key = f.d0key)
(6 rows)
?column?
----------
Query 2
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=341776.65..341777.42 rows=311 width=37)
-> Hash Join (cost=618.13..341582.54 rows=25880 width=37)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=394.34..340424.95 rows=135001 width=37)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..288680.40 rows=10000040 width=12)
-> Hash (cost=394.00..394.00 rows=135 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..394.00 rows=135 width=33)
Filter: (((dattyp)::text >= '10th measure type'::text) AND ((dattyp)::text <= '14th measure type'::text))
-> Hash (cost=219.00..219.00 rows=1917 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=1917 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
(12 rows)
?column?
----------
Query 3
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=342382.53..342415.51 rows=13192 width=66)
-> Hash Join (cost=1024.87..342250.61 rows=13192 width=66)
Hash Cond: ("outer".d2key = "inner".d2key)
-> Hash Join (cost=618.13..341582.54 rows=25880 width=41)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=394.34..340424.95 rows=135001 width=41)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..288680.40 rows=10000040 width=16)
-> Hash (cost=394.00..394.00 rows=135 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..394.00 rows=135 width=33)
Filter: (((dattyp)::text >= '10th measure type'::text) AND ((dattyp)::text <= '14th measure type'::text))
-> Hash (cost=219.00..219.00 rows=1917 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=1917 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
-> Hash (cost=394.00..394.00 rows=5097 width=33)
-> Seq Scan on dim2 d2 (cost=0.00..394.00 rows=5097 width=33)
Filter: (((dattyp)::text >= '1th measure type'::text) AND ((dattyp)::text <= '4th measure type'::text))
(17 rows)
?column?
----------
Query 4
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=388893.14..388893.17 rows=11 width=8)
-> Hash Join (cost=202.34..372223.08 rows=3334014 width=8)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Seq Scan on fact0 f (cost=0.00..288680.40 rows=10000040 width=8)
-> Hash (cost=194.00..194.00 rows=3334 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..194.00 rows=3334 width=8)
Filter: (dyr < 2010)
(7 rows)
?column?
----------
Query 0
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=287105.63..287105.64 rows=1 width=8)
-> Nested Loop (cost=0.00..285543.12 rows=312502 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=314 width=8)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate <= '2010-12-28'::date))
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..894.96 rows=1099 width=8)
Index Cond: ("outer".d0key = f.d0key)
(6 rows)
?column?
----------
Query 1
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=306267.49..306267.49 rows=1 width=8)
-> Nested Loop (cost=0.00..304600.48 rows=333402 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..189.60 rows=335 width=8)
Filter: (dyr = 2010)
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..894.96 rows=1099 width=8)
Index Cond: ("outer".d0key = f.d0key)
(6 rows)
?column?
----------
Query 2
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=338425.14..338426.62 rows=591 width=37)
-> Hash Join (cost=609.32..338057.97 rows=48957 width=37)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=390.41..335146.89 rows=293680 width=37)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=12)
-> Hash (cost=389.67..389.67 rows=295 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..389.67 rows=295 width=33)
Filter: (((dattyp)::text >= '10th measure type'::text) AND ((dattyp)::text <= '14th measure type'::text))
-> Hash (cost=214.72..214.72 rows=1675 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=1675 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
(12 rows)
?column?
----------
Query 3
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=339211.51..339274.77 rows=25305 width=66)
-> Hash Join (cost=1011.97..338958.46 rows=25305 width=66)
Hash Cond: ("outer".d2key = "inner".d2key)
-> Hash Join (cost=609.32..338057.97 rows=48957 width=41)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=390.41..335146.89 rows=293680 width=41)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=16)
-> Hash (cost=389.67..389.67 rows=295 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..389.67 rows=295 width=33)
Filter: (((dattyp)::text >= '10th measure type'::text) AND ((dattyp)::text <= '14th measure type'::text))
-> Hash (cost=214.72..214.72 rows=1675 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=1675 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
-> Hash (cost=389.67..389.67 rows=5192 width=33)
-> Seq Scan on dim2 d2 (cost=0.00..389.67 rows=5192 width=33)
Filter: (((dattyp)::text >= '1th measure type'::text) AND ((dattyp)::text <= '4th measure type'::text))
(17 rows)
?column?
----------
Query 4
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=387029.70..387029.73 rows=12 width=8)
-> Hash Join (cost=198.81..368692.63 rows=3667414 width=8)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=8)
-> Hash (cost=189.60..189.60 rows=3685 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..189.60 rows=3685 width=8)
Filter: (dyr < 2010)
(7 rows)
?column?
----------
Query 0
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Aggregate (cost=324144.19..326772.84 rows=35049 width=16)
-> Group (cost=324144.19..325896.62 rows=350487 width=16)
-> Sort (cost=324144.19..325020.41 rows=350487 width=16)
Sort Key: d0.dmth
-> Nested Loop (cost=0.00..291866.09 rows=350487 width=16)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=352 width=8)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate <= '2010-12-28'::date))
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..815.63 rows=1003 width=8)
Index Cond: ("outer".d0key = f.d0key)
(9 rows)
?column?
----------
Query 1
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Aggregate (cost=308145.92..310645.93 rows=33333 width=16)
-> Group (cost=308145.92..309812.59 rows=333335 width=16)
-> Sort (cost=308145.92..308979.25 rows=333335 width=16)
Sort Key: d0.dmth
-> Nested Loop (cost=0.00..277568.09 rows=333335 width=16)
-> Seq Scan on dim0 d0 (cost=0.00..189.60 rows=335 width=8)
Filter: (dyr = 2010)
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..815.63 rows=1003 width=8)
Index Cond: ("outer".d0key = f.d0key)
(9 rows)
?column?
----------
Query 2
(1 row)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=342751.95..343304.76 rows=5528 width=53)
-> Group (cost=342751.95..343166.56 rows=55281 width=53)
-> Sort (cost=342751.95..342890.15 rows=55281 width=53)
Sort Key: d0.dmth, d1.dat
-> Hash Join (cost=609.76..338397.30 rows=55281 width=53)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=390.43..335979.29 rows=301534 width=45)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=12)
-> Hash (cost=389.67..389.67 rows=303 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..389.67 rows=303 width=33)
Filter: ((dattyp >= '10th measure type'::character varying) AND (dattyp <= '14th measure type'::character varying))
-> Hash (cost=214.72..214.72 rows=1842 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=1842 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
(15 rows)
?column?
----------
Query 3
(1 row)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=341504.08..341855.37 rows=2810 width=90)
-> Group (cost=341504.08..341785.11 rows=28103 width=90)
-> Sort (cost=341504.08..341574.33 rows=28103 width=90)
Sort Key: d0.dmth, d1.dat, d2.dat
-> Hash Join (cost=1012.20..339427.44 rows=28103 width=90)
Hash Cond: ("outer".d2key = "inner".d2key)
-> Hash Join (cost=609.76..338397.30 rows=55281 width=57)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Hash Join (cost=390.43..335979.29 rows=301534 width=49)
Hash Cond: ("outer".d1key = "inner".d1key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=16)
-> Hash (cost=389.67..389.67 rows=303 width=33)
-> Seq Scan on dim1 d1 (cost=0.00..389.67 rows=303 width=33)
Filter: ((dattyp >= '10th measure type'::character varying) AND (dattyp <= '14th measure type'::character varying))
-> Hash (cost=214.72..214.72 rows=1842 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..214.72 rows=1842 width=8)
Filter: ((dyr >= 2010) AND (dyr <= 2015))
-> Hash (cost=389.67..389.67 rows=5107 width=33)
-> Seq Scan on dim2 d2 (cost=0.00..389.67 rows=5107 width=33)
Filter: ((dattyp >= '1th measure type'::character varying) AND (dattyp <= '4th measure type'::character varying))
(20 rows)
?column?
----------
Query 4
(1 row)
QUERY PLAN
-------------------------------------------------------------------------------------------
Aggregate (cost=788509.46..814759.58 rows=350002 width=16)
-> Group (cost=788509.46..806009.54 rows=3500016 width=16)
-> Sort (cost=788509.46..797259.50 rows=3500016 width=16)
Sort Key: d0.dyr
-> Hash Join (cost=198.39..375768.26 rows=3500016 width=16)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Seq Scan on fact0 f (cost=0.00..281819.45 rows=10000045 width=8)
-> Hash (cost=189.60..189.60 rows=3517 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..189.60 rows=3517 width=8)
Filter: (dyr < 2010)
(10 rows)
?column?
----------
Query 0
(1 row)
NOTICE: QUERY PLAN:
Aggregate (cost=443426.54..443676.54 rows=5000 width=16)
-> Group (cost=443426.54..443551.54 rows=50000 width=16)
-> Sort (cost=443426.54..443426.54 rows=50000 width=16)
-> Hash Join (cost=219.12..439524.12 rows=50000 width=16)
-> Seq Scan on fact0 f (cost=0.00..288680.00 rows=10000000 width=8)
-> Hash (cost=219.00..219.00 rows=50 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=50 width=8)
EXPLAIN
?column?
----------
Query 1
(1 row)
NOTICE: QUERY PLAN:
Aggregate (cost=443401.54..443651.54 rows=5000 width=16)
-> Group (cost=443401.54..443526.54 rows=50000 width=16)
-> Sort (cost=443401.54..443401.54 rows=50000 width=16)
-> Hash Join (cost=194.12..439499.12 rows=50000 width=16)
-> Seq Scan on fact0 f (cost=0.00..288680.00 rows=10000000 width=8)
-> Hash (cost=194.00..194.00 rows=50 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..194.00 rows=50 width=8)
EXPLAIN
?column?
----------
Query 2
(1 row)
NOTICE: QUERY PLAN:
Aggregate (cost=440666.53..440668.41 rows=25 width=92)
-> Group (cost=440666.53..440667.78 rows=250 width=92)
-> Sort (cost=440666.53..440666.53 rows=250 width=92)
-> Hash Join (cost=598.45..440656.58 rows=250 width=92)
-> Hash Join (cost=379.33..439684.33 rows=50000 width=84)
-> Seq Scan on fact0 f (cost=0.00..288680.00 rows=10000000 width=12)
-> Hash (cost=379.20..379.20 rows=50 width=72)
-> Index Scan using dim1_d1key on dim1 d1 (cost=0.00..379.20 rows=50 width=72)
-> Hash (cost=219.00..219.00 rows=50 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=50 width=8)
EXPLAIN
?column?
----------
Query 3
(1 row)
NOTICE: QUERY PLAN:
Aggregate (cost=441039.68..441039.69 rows=1 width=168)
-> Group (cost=441039.68..441039.69 rows=1 width=168)
-> Sort (cost=441039.68..441039.68 rows=1 width=168)
-> Hash Join (cost=977.78..441039.67 rows=1 width=168)
-> Hash Join (cost=598.45..440656.58 rows=250 width=96)
-> Hash Join (cost=379.33..439684.33 rows=50000 width=88)
-> Seq Scan on fact0 f (cost=0.00..288680.00 rows=10000000 width=16)
-> Hash (cost=379.20..379.20 rows=50 width=72)
-> Index Scan using dim1_d1key on dim1 d1 (cost=0.00..379.20 rows=50 width=72)
-> Hash (cost=219.00..219.00 rows=50 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=50 width=8)
-> Hash (cost=379.20..379.20 rows=50 width=72)
-> Index Scan using dim2_d2key on dim2 d2 (cost=0.00..379.20 rows=50 width=72)
EXPLAIN
?column?
----------
Query 4
(1 row)
NOTICE: QUERY PLAN:
Aggregate (cost=2021869.90..2038536.57 rows=333333 width=16)
-> Group (cost=2021869.90..2030203.24 rows=3333333 width=16)
-> Sort (cost=2021869.90..2021869.90 rows=3333333 width=16)
-> Merge Join (cost=1562901.63..1629790.83 rows=3333333 width=16)
-> Index Scan using dim0_d0key on dim0 d0 (cost=0.00..214.20 rows=3333 width=8)
-> Sort (cost=1562901.63..1562901.63 rows=10000000 width=8)
-> Seq Scan on fact0 f (cost=0.00..288680.00 rows=10000000 width=8)
EXPLAIN