Postgres查询执行时间 [英] Postgres Query execution time

问题描述

在 MySQL 命令行界面中，当您执行查询时，它将告诉您在打印出结果后查询执行了多长时间。

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

在Postgres命令行界面（ psql ）中不会告诉您。我知道如何配置日志记录，以便可以从日志中获取信息，但是像在 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.

可以做到吗？

推荐答案

使用 \timing ，如如何使用psql计时SQL查询？所述>。

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

另请参见 psql 的手册。

See also the manual for psql.

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

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

您还可以使用 EXPLAIN ANALYZE 获取详细的执行时间。除非您使用 EXPLAIN（ANALYZE TRUE，TIMING FALSE）（这仅在较新的版本中）并且禁用详细定时以仅提供总计执行时间，否则这会花费一些定时开销。 。

You can also use EXPLAIN ANALYZE to 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.

PgBadger ，尤其是与 auto_explain 模块，可以从日志分析中提供有用的汇总统计信息。

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

最后，还有 pg_stat_statements ，可以在运行的系统上收集方便的汇总信息。

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

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