arrayanalyze.php

<?php
/*
 * CREATE TABLE test_result (
 *   tablename      text,
 *   value          int[],
 *   operator       text,
 *   estimated_count int,
 *   actual_count   int
 * );
 */

/*
 * Description of testing tables.
 * table_name => table_desription
 *   count      - count of rows in table
 *   maxElement - parameter of distribution
 *   pow        - parameter of distribution
 * Numbers are assumed to be generated as random()^pow * maxElement
 */
$testTables = array(
  'array_test1' => array(
    'count'      => 10000,
    'maxElement' => 100,
    'pow'        => 1
  ),
  'array_test2' => array(
    'count'      => 10000,
    'maxElement' => 1500,
    'pow'        => 2
  ),
  'array_test3' => array(
    'count'      => 10000,
    'maxElement' => 5000,
    'pow'        => 3
  )
);

$testsCount = 100;
$operators = array('<@', '@>', '&&');

/*
 * Database connection options.
 */
$dbConnString = 'pgsql:host=localhost;dbname=test;port=5431';
$dbUsername = 'smagen';
$dbPassword = '';

/*
 * Generate test array.
 */
function genArray($maxN, $count, $pow)
{
  $range = 1.0;
  $result = array();
  for ($i = 0; $i < $count; $i++)
  {
    $value = rand() / getrandmax() * $range;
    $value = intval(pow($value, $pow) * $maxN);
    for ($j = 0; $j < $i; $j++)
    {
      if ($result[$j] <= $value)
        $value++;
    }
    $result []= $value;
    $range -= pow(($value + 1) / $maxN, 1 / $pow) - pow($value / $maxN, 1 / $pow);
    sort($result);
  }
  return $result;
}

/**
 * Returns number of extimated and actual numbers of rows.
 * 
 * @global PDO $con Соединение с БД
 * @param string $query Запрос
 * @return integer
 */
function getRowsCount($query)
{
  global $con;
  /* Get plan of query execution in JSON format */
  $result = $con->query('EXPLAIN (ANALYZE, 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(
    'estimate' => $plan['Plan Rows'],
    'actual'   => $plan['Actual Rows']
  );
}

/*
 * Connect to the test database.
 */
$con = new PDO($dbConnString, $dbUsername);

/*
 * Prepared statement for new test information insertion.
 */
$testInsert = $con->prepare('
  INSERT INTO test_result
  (
    tablename,
    value,
    operator,
    estimated_count,
    actual_count
  )
  VALUES
  (
    :tablename,
    :value,
    :operator,
    :estimated_count,
    :actual_count
  );
');

/*
 * Interate over all test tables.
 */
foreach ($testTables as $tableName => $tableInfo)
{
  $maxElement = $tableInfo['maxElement'];
  $pow = $tableInfo['pow'];
  /*
   * Generate $testCount tests for each table.
   */
  for ($i = 1; $i <= $testsCount; $i++)
  {
    /*
     * Interate over supported operators.
     */
    foreach ($operators as $operator)
    {
      $testValue = genArray($maxElement, $i, $pow);

      /*
       * Measure estimated and actual count.
       */
      $query = '
        SELECT
          *
        FROM 
          ' . $tableName . '
        WHERE
          value ' . $operator . ' ARRAY[' . implode(',', $testValue) . ']';
      $count = getRowsCount($query);

      /*
       * Insert test result.
       */
      $testInsert->execute(array(
        'tablename'      => $tableName,
        'value'          => '{' . implode(',', $testValue) . '}',
        'operator'       => $operator,
        'estimated_count' => $count['estimate'],
        'actual_count'   => $count['actual']
      ));
    }
  }
}