These are benchmark results for the following :
All were compiled from source using gcc 3.3.4. No special compiler flags were used for Postgres and Firebird - autoconf seemed to do a good enough job. For Mysql the recommended autoconf options for FreeBSD were used. A Firebird classic server was built.
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 - i.e no time was spent trying to discover if some of the servers would perform better with differing index configurations.
Remember these are results particular to these versions of the servers, the hardware plus os they were run on, and are intended to be informational (maybe provoking some discussion) rather than a crusade about which product is the 'best one'.
The benchmark was performed on 23/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 |
Despite being an old machine, the well equipped memory and io subsystems meant it was quite suitable for this sort of activity.
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 | Postgres | Firebird | Mysql |
|---|---|---|---|
| Load | 401s | 1540s | 676s |
| Index | 643s | 524s | 0s |
| Query0 | 5s | 10s | 10s |
| Query1 | 3s | 9s | 10s |
| Query2 | 39s | 70s | 131s |
| Query3 | 36s | 77s | 89s |
| Query4 | 49s | 90s | 98s |
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 | Postgres | Firebird | Mysql |
|---|---|---|---|
| Query0 | 25s | 32s | 29s |
| Query1 | 3s | 9s | 10s |
| Query2 | 52s | 137s | 196s |
| Query3 | 36s | 77s | 89s |
| Query4 | 49s | 221s | 224s |
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. Note that for Firebird these were generated without the AS qualifier in the FROM clause.
--
-- 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 0
-------
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)
Query 1
-------
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)
Query 2
-------
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)
Query 3
-------
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)
Query 4
-------
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)
Query 0
-------
PLAN SORT (JOIN (D0 NATURAL,F INDEX (FACT0_D0KEY)))
Query 1
-------
PLAN SORT (JOIN (D0 NATURAL,F INDEX (FACT0_D0KEY)))
Query 2
-------
PLAN SORT (JOIN (D0 NATURAL,F INDEX (FACT0_D0KEY),D1 INDEX (DIM1_D1KEY)))
Query 3
-------
PLAN SORT (JOIN (D0 NATURAL,F INDEX (FACT0_D0KEY),D2 INDEX (DIM2_D2KEY),D1 INDEX (DIM1_D1KEY)))
Query 4
-------
PLAN SORT (JOIN (D0 NATURAL,F INDEX (FACT0_D0KEY)))
+---------+
| Query 0 |
+---------+
| Query 0 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | d0 | ALL | dim0_d0key | NULL | NULL | NULL | 10000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | f | ref | fact0_d0key | fact0_d0key | 4 | benchw.d0.d0key | 1000 | |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 1 |
+---------+
| Query 1 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | d0 | ALL | dim0_d0key | NULL | NULL | NULL | 10000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | f | ref | fact0_d0key | fact0_d0key | 4 | benchw.d0.d0key | 1000 | |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 2 |
+---------+
| Query 2 |
+---------+
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | d0 | ALL | dim0_d0key | NULL | NULL | NULL | 10000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | f | ref | fact0_d0key,fact0_d1key | fact0_d0key | 4 | benchw.d0.d0key | 1000 | |
| 1 | SIMPLE | d1 | eq_ref | dim1_d1key | dim1_d1key | 4 | benchw.f.d1key | 1 | Using where |
+----+-------------+-------+--------+-------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 3 |
+---------+
| Query 3 |
+---------+
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | d0 | ALL | dim0_d0key | NULL | NULL | NULL | 10000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | f | ref | fact0_d0key,fact0_d1key,fact0_d2key | fact0_d0key | 4 | benchw.d0.d0key | 1000 | |
| 1 | SIMPLE | d2 | eq_ref | dim2_d2key | dim2_d2key | 4 | benchw.f.d2key | 1 | Using where |
| 1 | SIMPLE | d1 | eq_ref | dim1_d1key | dim1_d1key | 4 | benchw.f.d1key | 1 | Using where |
+----+-------------+-------+--------+-------------------------------------+-------------+---------+-----------------+-------+----------------------------------------------+
+---------+
| Query 4 |
+---------+
| Query 4 |
+---------+
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | d0 | ALL | dim0_d0key | NULL | NULL | NULL | 10000 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | f | ref | fact0_d0key | fact0_d0key | 4 | benchw.d0.d0key | 1000 | |
+----+-------------+-------+------+---------------+-------------+---------+-----------------+-------+----------------------------------------------+