range_test.php
application/x-httpd-php
Filename: range_test.php
Type: application/x-httpd-php
Part: 1
<?php
/* Connection options */
$dbConnString = 'pgsql:host=/tmp;dbname=test;port=5431';
$dbUsername = 'smagen';
$dbPassword = '';
$con = new PDO($dbConnString, $dbUsername);
/*
* Let's use bitmap indexscan for all queries.
*/
$con->query('SET enable_seqscan = off;');
$con->query('SET enable_indexscan = off;');
$con->query('SET enable_indexonlyscan = off;');
/*
* Prepared statement for insertion of information about new index.
*/
$indexInsert = $con->prepare('
INSERT INTO indexes
(
distribution_type,
distr1,
param1,
distr2,
param2,
distr3,
param3,
opclass,
buildtime
)
VALUES
(
:distribution_type,
:distr1,
:param1,
:distr2,
:param2,
:distr3,
:param3,
:opclass,
:buildtime
)
RETURNING
id;
');
/*
* Prepared statement for particular test result insertion.
*/
$testInsert = $con->prepare('
INSERT INTO test_results
(
index_id,
test_id,
operator,
hits,
time,
count
)
VALUES
(
:index_id,
:test_id,
:operator,
:hits,
:time,
:count
);
');
/*
* Examined opclasses. This arrays defines for each opclass:
* - am - name of access method
* - name - name of opclass itself
*/
$opclasses = array(
array(
'am' => 'gist',
'name' => 'range_ops'
),
array(
'am' => 'gist',
'name' => 'range_ops2'
),
array(
'am' => 'spgist',
'name' => 'range_ops_quad'
),
array(
'am' => 'spgist',
'name' => 'range_ops_kd'
)
);
/*
* Operators which are using for testing. For each test range search using
* each operator are executed.
*/
$operators = array(
'&&',
'<@',
'@>'
);
/*
* Test ranges are selected from the database.
*/
$tests = array();
foreach ($con->query('SELECT * FROM tests') as $row)
{
$tests [$row['id']]= $row['value'];
}
/*
* Distribution which values can be used as start of range:
* - uniform - values are distributed uniformly in range [0; param]
* - exponential - values are distributed exponentially which given parameter
* - gaussian - values according gaussian distribution which mean = 0,
* variance = param
*/
$startDistributions = array(
'uniform',
'exponential',
'gaussian'
);
/*
* Distribution which values can be used as size of range:
* - uniform - values are distributed uniformly in range [0; param]
* - exponential - values are distributed exponentially which given parameter
* - gaussian_non_negative - values according gaussian distribution which
* mean = parameter, variance = param. Since we don't allow ranges with
* negative size, we use absolute value.
*/
$sizeDistributions = array(
'uniform',
'exponential',
'gaussian_non_negative'
);
/* Count of rows in each dataset */
$count = 1000000;
/* Count of clusters in cluster dataset */
$clustersCount = 20;
/**
* Returns number of IOs hits during query execution, time of query execution
* and count of found rows.
*
* @global PDO $con Database connection
* @param string $query Query
* @return integer
*/
function getHitsCount($query)
{
global $con;
/* Get plan of query execution in JSON format */
$result = $con->query('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' . $query);
if (!$result)
{
echo $query;
exit(1);
}
$row = $result->fetch(PDO::FETCH_NUM);
/* Decoding JSON */
$explain = json_decode($row[0], true);
$plan = $explain[0]['Plan'];
/* Find element of index scan */
if (isset($plan['Plans'][0]))
{
$plan = $plan['Plans'][0];
$hits = $plan['Shared Hit Blocks'];
$hits += $plan['Shared Read Blocks'];
}
else
{
$hits = $plan['Shared Hit Blocks'];
$hits += $plan['Shared Read Blocks'];
}
return array(
'hits' => $hits,
'time' => $explain[0]['Total Runtime'],
'count' => $explain[0]['Plan']['Actual Rows']
);
}
/**
* Performs tests on given dataset.
*
* @param type $tableSql SQL query for test dataset generation.
* @param type $params Params of test dataset.
*/
function testDistribution($tableSql, $params)
{
global $con, $tests, $opclasses, $operators, $indexInsert, $testInsert;
$con->query('DROP TABLE IF EXISTS ranges;');
$con->query($tableSql);
/* Iterate over all existing opclasses */
foreach ($opclasses as $opclass)
{
/* Create index and write information about it */
$con->query("DROP INDEX IF EXISTS ranges_idx;");
$startTime = microtime(true);
$con->query('
CREATE INDEX
ranges_idx
ON
ranges
USING
' . $opclass['am'] . '
(
value ' . $opclass['name']. '
)
');
$endTime = microtime(true);
$buildTime = $endTime - $startTime;
$params['opclass'] = $opclass['name'];
$params['buildtime'] = $buildTime;
$indexInsert->execute($params);
$row = $indexInsert->fetch(PDO::FETCH_ASSOC);
$indexId = $row['id'];
print_r($params);
/* Perform tests */
foreach ($tests as $testId => $testValue)
{
foreach ($operators as $operator)
{
$query = "
SELECT
*
FROM
ranges
WHERE
value $operator '$testValue'::floatrange;";
$hits = getHitsCount($query);
$testParams = array(
'index_id' => $indexId,
'test_id' => $testId,
'operator' => $operator,
'hits' => $hits['hits'],
'count' => $hits['count'],
'time' => $hits['time']
);
$testInsert->execute($testParams);
}
}
}
}
/* Possible distribution parameters for range size generation */
$sizeParams = array(
10,
100,
1000,
10000
);
/* Simple: ranges are distributed independently */
$startParam = 1000000;
foreach ($startDistributions as $startDistribution)
foreach ($sizeDistributions as $sizeDistribution)
foreach ($sizeParams as $sizeParam)
{
testDistribution("
CREATE TABLE
ranges AS
(
SELECT
*
FROM
generate_objects(
$count,
'$startDistribution',
$startParam,
'$sizeDistribution',
$sizeParam
) AS value
);
",
array(
'distribution_type' => 'simple',
'distr1' => $startDistribution,
'param1' => $startParam,
'distr2' => $sizeDistribution,
'param2' => $sizeParam,
'distr3' => NULL,
'param3' => NULL
));
}
/*
* Clusters: ranges are grouped into clusters. Mean offset of range in cluster
* is in clustersCount times less then range of clusters.
*/
$offsetParam = $startParam / $clustersCount;
foreach ($startDistributions as $centerDistribution)
foreach ($startDistributions as $offsetDistribution)
foreach ($sizeDistributions as $sizeDistribution)
foreach ($sizeParams as $sizeParam)
{
testDistribution("
CREATE TABLE
ranges AS
(
SELECT
*
FROM
generate_objects_clusters(
$count,
$clustersCount,
'$centerDistribution',
$startParam,
'$offsetDistribution',
$offsetParam,
'$sizeDistribution',
$sizeParam
) AS value
);
",
array(
'distribution_type' => 'clusters',
'distr1' => $centerDistribution,
'param1' => $startParam,
'distr2' => $offsetDistribution,
'param2' => $offsetParam,
'distr3' => $sizeDistribution,
'param3' => $sizeParam
));
}
/* Lifetime: ranges are produced by life simulation */
$lifetimeParam = $startParam / $clustersCount;
foreach ($startDistributions as $startDistribution)
foreach ($sizeDistributions as $lifetimeDistribution)
foreach ($sizeDistributions as $sizeDistribution)
foreach ($sizeParams as $sizeParam)
{
testDistribution("
CREATE TABLE
ranges AS
(
SELECT
*
FROM
generate_objects_clusters(
$count,
$clustersCount,
'$startDistribution',
$startParam,
'$lifetimeDistribution',
$lifetimeParam,
'$sizeDistribution',
$sizeParam
) AS value
);
",
array(
'distribution_type' => 'lifetime',
'distr1' => $centerDistribution,
'param1' => $startParam,
'distr2' => $lifetimeDistribution,
'param2' => $lifetimeParam,
'distr3' => $sizeDistribution,
'param3' => $sizeParam
));
}