代码之家 › 专栏 › 技术社区 › Donald Miner

查询时间统计(PostgreSQL)

postgresql performance sql

Donald Miner · 技术社区 · 14 年前

我有一个有10亿行的表,我想确定几个查询的平均时间和时间标准差:

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,执行查询,记录时间,然后将其存储在另一个表中。

一个完全不同的方法就可以了,只要我可以呆在PostgreSQL中(也就是说,我不想写一个外部程序来做这个)。

4 回复 | 直到 14 年前

Community CDub 4 年前

你知道 EXPLAIN statement ?

显示中最关键的部分是估计的语句执行成本,这是计划人员对运行语句所需时间的猜测(以磁盘页获取为单位)。实际上显示了两个数字:返回第一行之前的启动时间和返回所有行的总时间。对于大多数查询来说,总时间是最重要的,但是在诸如EXISTS中的子查询这样的上下文中,计划器将选择最小的启动时间,而不是最小的总时间(因为执行器在获得一行之后将停止)。另外,如果使用limit子句限制要返回的行数,则规划器会在端点成本之间进行适当的插值,以估计哪个计划真正最便宜。

这个 ANALYZE 选项使语句实际执行,而不仅仅是计划执行。每个计划节点内花费的总运行时间(以毫秒为单位)及其实际返回的总行数将添加到显示中。这有助于了解规划者的估计是否接近现实。

EXPLAIN ANALYZE

Pablo Santa Cruz 14 年前

您需要更改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 .

user832146 12 年前

直接,不,没有。但是你可以通过检查你感兴趣的查询前后的时间来做一个间接的非常接近的估计。

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

函数clock\u timestamp()提供语句开始时服务器的实际时间。由于SELECT不涉及表,所以我们可以期望它几乎是瞬时的。我猜任何Pg驱动程序都支持多个查询;重要的是这3个查询(真实的一个和2个额外的)同时进行,否则您将测量数据传输时间以及。。。

对于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中发送了多个查询)。

Daniel 14 年前

只需创建一个自己的volatile now()函数,在每次调用时返回另一个值即可。