获取PostgreSQL查询的执行时间 [英] Get execution time of PostgreSQL query

问题描述

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime=GETDATE()

select distinct born_on.name
from   born_on,died_on
where (FLOOR(('2012-01-30'-born_on.DOB)/365.25) <= (
    select max(FLOOR((died_on.DOD - born_on.DOB)/365.25))
    from   died_on, born_on
    where (died_on.name=born_on.name))
    )
and   (born_on.name <> All(select name from died_on))

SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]

我无法获取查询时间。相反，我得到以下错误：

I am unable to get the query time. Instead I get the following error:

sql:/home/an/Desktop/dbms/query.sql:9: ERROR:  syntax error at or near "@"
LINE 1: DECLARE @StartTime datetime,@EndTime datetime

推荐答案

出于测试目的，您还可以使用 EXPLAIN ANALYZE 。

For testing purposes you can also use EXPLAIN ANALYZE.

您可以像这样使用它来检查我修改后的查询实际上是否更快：

You can use it like this to check whether my adapted version of your query is, in fact, faster:

EXPLAIN ANALYZE
SELECT DISTINCT born_on.name
FROM   born_on b
WHERE  floor(('2012-01-30'::date - b.dob) / 365.25) <= (
    SELECT floor((max(d1.dod - b1.dob)/365.25))
    FROM   born_on b1
    JOIN   died_on d1 USING (name)
    )
AND NOT EXISTS (
    SELECT *
    FROM   died_on d2
    WHERE  d2.name = b.name
    );

显示除查询计划外的总运行时间。执行几次以排除工件。

A 几个选项可用以获取更多详细信息。

Shows the total runtime in addition to the query plan. Execute a couple of times to exclude artifacts.
A couple of options are available for more details.

这篇关于获取PostgreSQL查询的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！