range_stat.php
application/x-httpd-php
Filename: range_stat.php
Type: application/x-httpd-php
Part: 0
<?php
/* Connection options */
$dbConnString = 'pgsql:host=/tmp;dbname=test;port=5432';
$dbUsername = 'smagen';
$dbPassword = '';
$statTarget = 1000;
$con = new PDO($dbConnString, $dbUsername);
/*
* Prepared statement for insertion of information about dataset.
*/
$datasetInsert = $con->prepare('
INSERT INTO datasets
(
distribution_type,
distr1,
param1,
distr2,
param2,
distr3,
param3,
stat_target
)
VALUES
(
:distribution_type,
:distr1,
:param1,
:distr2,
:param2,
:distr3,
:param3,
:stat_target
)
RETURNING
id;
');
/*
* Prepared statement for particular test result insertion.
*/
$testInsert = $con->prepare('
INSERT INTO test_results
(
test_id,
dataset_id,
operator,
estimate_count,
actual_count
)
VALUES
(
:test_id,
:dataset_id,
:operator,
:estimate_count,
:actual_count
);
');
/*
* 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 estimated and actual counts of rows returned by given query.
*
* @global PDO $con Database connection
* @param string $query Query
* @return integer
*/
function getCounts($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'];
return array(
'actual_count' => $plan['Actual Rows'],
'estimate_count' => $plan['Plan 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, $operators, $datasetInsert, $testInsert, $statTarget;
$con->query('DROP TABLE IF EXISTS ranges;');
/*
* Create table by given query, set statistics, collect statistics and create
* index for faster testing.
*/
$con->query($tableSql);
$con->query('ALTER TABLE ranges ALTER COLUMN value SET STATISTICS ' . $statTarget . ';');
$con->query('ANALYZE ranges;');
$con->query('CREATE INDEX ranges_idx ON ranges USING gist (value);');
$params['stat_target'] = $statTarget;
$datasetInsert->execute($params);
$row = $datasetInsert->fetch(PDO::FETCH_ASSOC);
$datasetId = $row['id'];
/* Perform tests */
foreach ($tests as $testId => $testValue)
{
foreach ($operators as $operator)
{
$query = "
SELECT
*
FROM
ranges
WHERE
value $operator '$testValue'::floatrange;";
$counts = getCounts($query);
$params = array_merge($counts, array(
'test_id' => $testId,
'dataset_id' => $datasetId,
'operator' => $operator
));
$testInsert->execute($params);
}
}
}
/* 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
));
}