SQL 获取 PostgreSQL 查询的执行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9063402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get execution time of PostgreSQL query
提问by Erwin Brandstetter
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
回答by Peter
If you mean in psql, rather than some program you are writing, use \?for the help, and see:
如果您的意思是在 psql 中，而不是您正在编写的某个程序，请使用\?帮助，并查看：
\timing [on|off] toggle timing of commands (currently off)
And then you get output like:
然后你会得到如下输出：
# \timing on
Timing is on.
# select 1234;
?column?
----------
1234
(1 row)
Time: 0.203 ms
回答by Erwin Brandstetter
For testing purposes you can also use EXPLAIN ANALYZE.
出于测试目的，您还可以使用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
);
Shows the total runtime in addition to the query plan. Execute a couple of times to exclude artifacts.
A couple of optionsare available for more details.
除查询计划外，还显示总运行时间。执行几次以排除工件。
有几个选项可用于了解更多详细信息。
回答by filiprem
PostgreSQL is not Transact-SQL. These are two slightly different things.
PostgreSQL 不是 Transact-SQL。这是两件略有不同的事情。
In PostgreSQL, this would be something along the lines of
在 PostgreSQL 中，这将类似于
DO $proc$
DECLARE
StartTime timestamptz;
EndTime timestamptz;
Delta double precision;
BEGIN
StartTime := clock_timestamp();
PERFORM foo FROM bar; /* Put your query here, replacing SELECT with PERFORM */
EndTime := clock_timestamp();
Delta := 1000 * ( extract(epoch from EndTime) - extract(epoch from StartTime) );
RAISE NOTICE 'Duration in millisecs=%', Delta;
END;
$proc$;
On the other hand, measuring query time does not have to be this complicated. There are better ways:
另一方面，测量查询时间不必如此复杂。有更好的方法：
In postgres command line clientthere is a
\timingfeature which measures query time on client side (similar to duration in bottomright corner of SQL Server Management Studio).It's possible to record query duration in server log(for every query, or only when it lasted longer than X milliseconds).
It's possible to collect server-side timing for any single statement using the
EXPLAINcommand:EXPLAIN (ANALYZE, BUFFERS) YOUR QUERY HERE;
在postgres 命令行客户端中有一个
\timing功能可以测量客户端的查询时间（类似于 SQL Server Management Studio 右下角的持续时间）。可以 在服务器日志中记录查询持续时间（对于每个查询，或者仅当它持续超过 X 毫秒时）。
可以使用以下
EXPLAIN命令为任何单个语句收集服务器端计时：EXPLAIN (ANALYZE, BUFFERS) YOUR QUERY HERE;
