Simple PostgreSQL and MongoDB benchmark PHP script

pg_mongo_bench.php

<?php

const RECORDS  = 1000000;
const SEARCHES = 10000;

$pgsql = pg_connect('host=localhost dbname=test') or die('Failed to connect PostgreSQL');
$version = pg_version();
!version_compare($version['server'], '9.4', '<') or die('Need PostgreSQL 9.4 or later');
$mongo = (new MongoClient())->test->test;

$data_tpl = <<<EOD
{
	"id": %d,
	"c1": [%s],
	"f1": "%s",
	"f2": "%s"
}
EOD;

// PostgreSQL initialization
$sql_init = <<<EOD
DROP TABLE IF EXISTS test;
CREATE TABLE test (j jsonb);
EOD;
pg_query($sql_init) or die('PostgreSQL initialization failed');

// MongDB initialization
$mongo->drop();



// PostgreSQL Insert
$start = microtime(true);
pg_prepare('stmt', 'INSERT INTO test (j) VALUES ($1)');
for($i = 0; $i < RECORDS; $i++) {
	$data = sprintf(
		$data_tpl,
		$i,
		join(',', array(mt_rand(1, 1000), mt_rand(1, 1000), mt_rand(1, 1000),mt_rand(1, 1000),mt_rand(1, 1000))),
		microtime(true),
		microtime(true)
	);
	pg_execute('stmt', array($data)) or die('PostgreSQL: Failed to insert');
}
echo "PostgreSQL Insert:\t".(microtime(true) - $start).PHP_EOL;

// MongoDB Insert
$start = microtime(true);
for($i = 0; $i < RECORDS; $i++) {
	$data = sprintf(
		$data_tpl,
		$i,
		join(',', array(mt_rand(1, 1000), mt_rand(1, 1000), mt_rand(1, 1000),mt_rand(1, 1000),mt_rand(1, 1000))),
		microtime(true),
		microtime(true)
	);
	$mongo->insert(json_decode($data)) or die('MongoDB: Failed to insert');
}
echo "MongoDB Insert:\t\t".(microtime(true) - $start).PHP_EOL;


// PostgreSQL Create Index
$start = microtime(true);
pg_query('CREATE INDEX test_j_gin_idx ON test USING gin (j)') or die('PostgreSQL CREATE INDEX failed');
echo "PostgreSQL Index:\t".(microtime(true) - $start).PHP_EOL;

// MongoDB Create Index
$start = microtime(true);
$mongo->ensureIndex(['id'=>1]) or die('MongoDB INDEX failed');
$mongo->ensureIndex(['c1'=>1]) or die('MongoDB INDEX failed');
echo "MongoDB Index:\t\t".(microtime(true) - $start).PHP_EOL;


// PostgreSQL Simple search
$start = microtime(true);
pg_prepare('search', 'SELECT * FROM test WHERE j @> $1');
for ($i = 0; $i < SEARCHES; $i++) {
	$result = pg_execute('search', ['{"id": '.mt_rand(0, RECORDS-1).'}']) or die('PostgreSQL search failed');
}
//var_dump(pg_fetch_all($result));
echo "PostgreSQL Search:\t".(microtime(true) - $start).PHP_EOL;

// MongoDB Simple search
$start = microtime(true);
for ($i = 0; $i < SEARCHES; $i++) {
	$result = $mongo->findOne(["id"=> mt_rand(0, RECORDS-1)]) or die('MongoDB search failed');
}
//var_dump($result);
echo "MongoDB Search:\t\t".(microtime(true) - $start).PHP_EOL;


// PostgreSQL Tag search
$start = microtime(true);
pg_prepare('tag', 'SELECT * FROM test WHERE j @> $1');
for ($i = 0; $i < SEARCHES; $i++) {
	$result = pg_execute('tag', ['{"c1": ['.join(',', [mt_rand(1, 1000), mt_rand(1, 1000)]).'] }']) or die('PostgreSQL search failed');
	//if (pg_num_rows($result)) var_dump(pg_fetch_all($result));
}
echo "PostgreSQL Tag Search:\t".(microtime(true) - $start).PHP_EOL;

// MongoDB Tag search
$start = microtime(true);
for ($i = 0; $i < SEARCHES; $i++) {
	$result = $mongo->find(["c1"=> ['$all'=>[mt_rand(1,1000), mt_rand(1, 1000)]]]) or die('MongoDB search failed');
	iterator_to_array($result); // PostgreSQL fetches result. To be fair, result is fetched.
	//$v = $result->getNext; if ($v) var_dump($v);
}
echo "MongoDB Tag Search:\t".(microtime(true) - $start).PHP_EOL;