是否可以获取在postgres中进行的查询的历史记录 [英] Is it possible to get a history of queries made in postgres

问题描述

是否可以获取在postgres中进行的查询的历史记录?是否有可能获得每个查询所花费的时间?我目前正在尝试识别正在处理的应用程序中的慢查询.

Is it possible to get a history of queries made in postgres? and is it be possible to get the time it took for each query? I'm currently trying to identify slow queries in the application I'm working on.

我正在使用Postgres 8.3.5

I'm using Postgres 8.3.5

推荐答案

数据库本身没有历史记录，如果您使用的是psql，则可以使用"\ s"在此处查看命令历史记录.

There's no history in the database itself, if you're using psql you can use "\s" to see your command history there.

通过设置 log_min_duration_statement ，如果将其设置为0，则会在日志中记录所有查询及其持续时间.一旦您的应用上线，这可能会有所帮助.如果将其设置为较高的值，则只会看到长期运行的查询，这对优化很有帮助(您可以对在那里找到的查询运行EXPLAIN ANALYZE，以了解它们的原因太慢了.

You can get future queries or other types of operations into the log files by setting log_statement in the postgresql.conf file. What you probably want instead is log_min_duration_statement, which if you set it to 0 will log all queries and their durations in the logs. That can be helpful once your apps goes live, if you set that to a higher value you'll only see the long running queries which can be helpful for optimization (you can run EXPLAIN ANALYZE on the queries you find there to figure out why they're slow).

在这方面要知道的另一个方便的事情是，如果运行psql并告诉它"\ timing"，它将显示此后每个语句要花费多长时间.因此，如果您有一个如下所示的sql文件:

Another handy thing to know in this area is that if you run psql and tell it "\timing", it will show how long every statement after that takes. So if you have a sql file that looks like this:

\timing
select 1;

您可以使用正确的标志运行它，并查看每个语句与花费了多长时间交织在一起.结果如下所示:

You can run it with the right flags and see each statement interleaved with how long it took. Here's how and what the result looks like:

$ psql -ef test.sql 
Timing is on.
select 1;
 ?column? 
----------
        1
(1 row)

Time: 1.196 ms

这很方便，因为与更改配置文件不同，您不需要成为数据库超级用户即可使用它，并且如果您要开发新代码并想对其进行测试，则使用起来会更容易.

This is handy because you don't need to be database superuser to use it, unlike changing the config file, and it's easier to use if you're developing new code and want to test it out.

这篇关于是否可以获取在postgres中进行的查询的历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！