统计数据的查询时间（PostgreSQL的）

问题描述：

我有一个十亿行的表，我想确定的平均时间与时间的标准偏差为形式的几个疑问：统计数据的查询时间（PostgreSQL的）

select * from mytable where col1 = '36e2ae77-43fa-4efa-aece-cd7b8b669043'; 
select * from mytable where col1 = '4b58c002-bea4-42c9-8f31-06a499cabc51'; 
select * from mytable where col1 = 'b97242ae-9f6c-4f36-ad12-baee9afae194'; 

....

我有一千个存储在另一个表中的col1的随机值。

是否有某种方式来存储多久，这些查询在一个单独的表了（毫秒），这样我就可以在它上面运行的一些统计数据？例如：对于我的随机表中的每个col1，执行查询，记录时间，然后将其存储在另一个表中。

完全不同的方法就可以了，只要我能留内的PostgreSQL（即我不想写一个外部程序来做到这一点）。

答

你知道EXPLAIN statement的？

该命令显示PostgreSQL规划器为所提供的语句生成的执行计划。执行规划显示如何通过语句引用的表（一个或多个）将被扫描 - 通过简单的顺序扫描，索引扫描等 - 如果引用了多个表，什么样的连接算法将被用来汇集来自各所要求的行输入表。

显示屏的最关键的部分是预计的语句执行成本，这是规划者它需要多长时间来运行该语句的猜测（磁盘页面抓取的单位来衡量）。实际上显示了两个数字：可以返回第一行之前的启动时间以及返回所有行的总时间。对于大多数查询的总时间是最重要的，但在环境下，比如一个EXISTS子查询，规划器将选择最小启动时间而不是最小总时间（因为执行器在获取一条记录后总是要停下来）。另外，如果你限制的行数与LIMIT子句返回，规划使得终端成本之间取得适当的插值计算哪个规划开销最省。

的ANALYZE选项将导致实际执行的语句，而不仅仅是规划。每个计划节点中花费的总时间（以毫秒为单位）和实际返回的总行数将被添加到显示中。这对于了解规划人员的估计是否接近实际很有用。

能很容易地编写一个脚本，您的查询每个表中的随机值确实的EXPLAIN ANALYZE，输出保存到一个文件/表格/等

有什么方法可以输出时间，这样我就不必解析文件了吗？这是我必须做的事，但似乎应该有一个更直接的方法。 – 2010-07-01 18:23:43

'psql -c“EXPLAIN ANALYZE select * from mytable where col1 ...”| grep“总运行时间”' – 2010-07-01 18:26:03

如果可能的话，我真的很想在SQL中完全做到这一点。似乎我应该能够将它在psql交互式shell中直接返回的运行时作为值存储。你的回答是非常正确的，而且如果没有人能给我一个纯粹的SQL答案，我一直在计划做什么。感谢您的时间！ – 2010-07-01 18:35:54

答

您需要更改PostgreSQL配置文件。

不启用该属性：

log_min_duration_statement = -1  # -1 is disabled, 0 logs all statements          
             # and their durations, > 0 logs only          
             # statements running at least this number         
             # of milliseconds

在此之后，执行时间将被记录，您将能够弄清楚到底有多糟糕（或好）正在执行查询。

您也可以使用一些日志分析工具来进行进一步的分析提供真棒HTML输出如pgfouine。

答

你不能做到这一点的SQL，因为即使你将能够每本声明呼吁在一个循环中，每次调用NOW（）将返回相同的结果，因为你是在一个单一的交易。

只需创建一个自己的易失性now（）函数，就可以在每次调用时返回另一个值。

答

直接，不，没有。但是，您可以通过在对时间感兴趣的查询之前和之后检查时间来做出间接且相当接近的估算。

$sql = "Your Query"; 
$bm = "SELECT extract(epoch FROM clock_timestamp())"; 
$query = "{$bm}; {$sql}; {$bm};";

功能clock_timestamp（）为您提供语句启动时的服务器实际时间。由于该SELECT没有涉及任何表格，我们可以预计它几乎是瞬间的。我想任何Pg驱动程序都支持多个查询;重要的是这3个查询（真正的一个和两个额外的）一起去，否则你会测量数据传输时间以及...

对于PHP我有一个函数来处理这个问题。总结如下：

<?php 

function pgquery($sql, $conn) 
{ 
    // Prepend and append benchmarking queries 
    $bm = "SELECT extract(epoch FROM clock_timestamp())"; 
    $query = "{$bm}; {$sql}; {$bm};"; 

    // Execute the query, and time it (data transport included) 
    $ini = microtime(true); 

    pg_send_query($conn, $query); 

    while ($resource = pg_get_result($conn)) 
    { 
     $resources[] = $resource; 
    } 

    $end = microtime(true); 

    // "Extract" the benchmarking results 
    $q_ini = pg_fetch_row(array_shift($resources)); 
    $q_end = pg_fetch_row(array_pop($resources)); 

    // Compute times 
    $time = round($end - $ini, 4);    # Total time (inc. transport) 
    $q_time = round($q_end[0] - $q_ini[0], 4); # Query time (Pg server only) 

    return $resources; 
} 

?>

我刚刚在那里留下了基础知识。 $ conn保存一个到Pg连接的链接，$ resources是一个返回的pg资源数组（如果你在$ sql中发送了多个查询）。

$ time保留查询离开Pg服务器以来的总时间，直到结果到达。 $ q-time只包含你想要的实际查询时间（或者非常好的近似值）。

添加错误处理和其他处理你喜欢，我有很多，但它与你的问题无关。

统计数据的查询时间（PostgreSQL的）

相关推荐