我可以在 PostgreSQL 8.4 中记录查询执行时间吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12670745/
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
Can I log query execution time in PostgreSQL 8.4?
提问by 9ine
I want to log each query execution time which is run in a day.
我想记录一天内运行的每个查询执行时间。
For example like this,
例如像这样，
2012-10-01 13:23:38 STATEMENT: SELECT * FROM pg_stat_database runtime:265 ms.
Please give me some guideline.
请给我一些指导。
回答by a_horse_with_no_name
If you set
如果你设置
log_min_duration_statement = 0
log_statement = all
in your postgresql.conf, then you will see all statements being logged into the Postgres logfile.
在您的 postgresql.conf 中，您将看到所有语句都被记录到 Postgres 日志文件中。
If you enable
如果您启用
log_duration
that will also print the time taken for each statement. This is off by default.
这也将打印每个语句所花费的时间。这是默认关闭的。
Using the log_statementparameter you can control which typeof statement you want to log (DDL, DML, ...)
使用该log_statement参数，您可以控制要记录的语句类型（DDL、DML...）
This will produce an output like this in the logfile:
这将在日志文件中产生这样的输出：
2012-10-01 13:00:43 CEST postgres LOG: statement: select count(*) from pg_class; 2012-10-01 13:00:43 CEST postgres LOG: duration: 47.000 ms
More details in the manual:
手册中的更多详细信息：
- http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
- http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
- http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
- http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
If you want a daily list, you probably want to configure the logfile to rotate on a daily basis. Again this is described in the manual.
如果您需要每日列表，您可能希望将日志文件配置为每天轮换。手册中再次对此进行了描述。
回答by Zoltán
I believe OP was actually asking for execution duration, not the timestamp.
我相信 OP 实际上是在要求执行持续时间，而不是时间戳。
To include the duration in the log output, open pgsql/<version>/data/postgresql.conf, find the line that reads
要在日志输出中包含持续时间，请打开pgsql/<version>/data/postgresql.conf，找到读取的行
#log_duration = off
and change it to
并将其更改为
log_duration = on
If you can't find the given parameter, just add it in a new line in the file.
如果找不到给定的参数，只需将其添加到文件的新行中。
After saving the changes, restart the postgresql service, or just invoke
保存更改后，重启 postgresql 服务，或者直接调用
pg_ctl reload -D <path to the directory of postgresql.conf>
e.g.
例如
pg_ctl reload -D /var/lib/pgsql/9.2/data/
to reload the configuration.
重新加载配置。
回答by Abdul Vajid
I think a better option is to enable pg_stat_statementsby enabling the PG stats extension. This will help you to find the query execution time for each query nicely recorded in a view
我认为更好的选择是pg_stat_statements通过启用 PG stats 扩展来启用。这将帮助您找到很好地记录在视图中的每个查询的查询执行时间
