postgresql Postgres 查询执行时间

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/17653884/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 00:17:05  来源:igfitidea点击:

Postgres Query execution time

postgresql

提问by NickJ

In the MySQLcommand line interface, when you execute a query it will tell you how long the query took to execute after printing out the results.

MySQL命令行界面中,当您执行查询时,它会告诉您在打印出结果后执行查询需要多长时间。

In the Postgres command line interface (psql) it does not tell you. I know how to configure the logging so I can get the information from logs, but it would be more convenient to have it print to standard output like it does in MySQL.

在 Postgres 命令行界面 ( psql) 中,它不会告诉您。我知道如何配置日志记录以便我可以从日志中获取信息,但是将它打印到标准输出会更方便,就像在MySQL.

Can this be done?

这能做到吗?

回答by Craig Ringer

Use \timingas explained by "How can I time SQL-queries using psql?".

使用\timing由解释说:“我的时间SQL查询如何使用PSQL?” .

See also the manual for psql.

另请参阅手册psql

If you want server-sideexecution times that don't include the time to transfer the result to the client, you can set log_min_duration_statement = 0in the configuration, then SET client_min_messages = logso you get the log info in the console.

如果您希望服务器端执行时间不包括将结果传输到客户端的时间,您可以log_min_duration_statement = 0在配置中进行设置,然后SET client_min_messages = log在控制台中获取日志信息。

You can also use EXPLAIN ANALYZEto get detailed execution timings. There's some timing overhead for this unless you use EXPLAIN (ANALYZE TRUE, TIMING FALSE), which is only in newer versions, and disables detailed timing to give only an aggregate execution time instead.

您还可以使用EXPLAIN ANALYZE获取详细的执行时间。除非您使用EXPLAIN (ANALYZE TRUE, TIMING FALSE),否则会有一些计时开销,这仅在较新版本中使用,并禁用详细计时以仅提供聚合执行时间。

PgBadger, especially when combined with the auto_explainmodule, can provide useful aggregate statistics from log analysis.

PgBadger,特别是当与结合auto_explain模块,可以提供从日志分析有用的汇总统计。

Finally, there's pg_stat_statements, which can collect handy aggregate information on the running system.

最后,还有pg_stat_statements,它可以收集有关正在运行的系统的方便的聚合信息。

回答by Vinoth Shankar

I think that EXPLAIN ANALYSEmight be helpful to you

我认为这EXPLAIN ANALYSE可能对你有帮助

Syntax:

句法:

EXPLAIN ANALYSE query;

Example;

例子;

EXPLAIN ANALYSE 
SELECT  * 
FROM    demotable;

Output:

输出:

"Seq Scan on demotable  (cost=0.00..12.10 rows=210 width=356) 
                        (actual time=0.020..0.021 rows=8 loops=1)"
"Planning time: 18.477 ms"
"Execution time: 0.042 ms"