Postgres: How can I see all SQL statements being executed by the database server?

Question

I'm in the process of reviewing every SQL statement that an application makes against the database, for performance reasons. Is there an easy way to log all statements that are executed by the PostgreSQL database server? Thanks.

In this following explanation is explained about how to see current running query http://www.tutorialdba.com/2016/11/pgstatactivity-view-explanation-in.html — nijam, Nov 09 '17 at 10:01

score 31 · Accepted Answer · edited Mar 21 '13 at 12:37

31

The config option you're looking for is log_statement = "all" (if you just want the statements), or log_min_statement_duration = <some number> if you're just after "slow" queries (for some value of "slow"). See http://www.postgresql.org/docs/current/static/runtime-config-logging.html for more details on logging configuration.

edited Mar 21 '13 at 12:37

Craig Ringer

10,553
9
38
59

answered Jun 04 '09 at 23:07

womble

95,029
29
173
228

1

Logging all statements is a performance killer (as stated in the official docs). However, 8.4 has a nice feature of getting the `explain analyze` of a slow query at the tie it was executed, you might want to start testing with this as 8.4 isn't yet released but it's a nice option to know that happend **at the time of execution**, if the analyze explain output is OK you probably are running into issues with I/O or CPU bounds, but at least you'll know it's not the query itself – Martin M. Jun 14 '09 at 14:05
6

I really like the log_statement = 'mod' option. It only shows creates, updates, and deletes, and skips all the select statements. Great if you're trying to figure out which code is tweaking some field. – Don Kirkby Aug 06 '09 at 21:48

score 5 · Answer 2 · answered Mar 21 '13 at 12:28

5

The auto_explain module is very useful for this. It'll not only log the statements, it'll log their execution plans and can even log statements run within PL/PgSQL functions. The performance hit is fairly low unless you enable analyze, in which case you incur a fair bit of timing overhead for all queries.

See auto_explain in the documentation.

answered Mar 21 '13 at 12:28

Craig Ringer

10,553
9
38
59

Note that "auto_explain" is a module, thus it doesn't run on AWS RDS Postgres databases :( – johntellsall Jan 09 '19 at 19:04
1

@johntellsall RDS supports `auto_explain`, it's in their list of blessed modules. See the documentation at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html – Craig Ringer Jan 10 '19 at 01:03

score 3 · Answer 3 · edited May 10 '18 at 17:35

3

Of course, you can detect slowest queries by yourself, but I advise you to use pgFouine — a PostgreSQL log analyzer. It`s easy to install and really useful.

Sample reports: here and here.

edited May 10 '18 at 17:35

Gregory Arenius

103
3

answered Jun 04 '09 at 21:53

Severe_admin

338
1
5

I used pgFouine on a project and identified several places where an index would help things a lot. – Paul Tomblin Jun 04 '09 at 21:54

Postgres: How can I see all SQL statements being executed by the database server?

3 Answers3