Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I log query execution time in PostgreSQL 8.4?

Tags:

postgresql

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.

like image 651
9ine Avatar asked Oct 01 '12 09:10

9ine


People also ask

How do you analyze a query performance in PostgreSQL?

The ANALYZE option causes the statement to be actually executed, not only planned. The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually returned are added to the display. This is useful for seeing whether the planner's estimates are close to reality.

How do I get last 24 hours record in PostgreSQL?

Get rows from past 24 hours in PostgreSQL In the above SQL query, we use PostgreSQL system function now() to get current datetime. Then we use INTERVAL clause to select those rows where order_date falls within past 24 hours of present datetime. You can also specify time interval in days, instead of hours.


2 Answers

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.

If you enable

log_duration

that will also print the time taken for each statement. This is off by default.

Using the log_statement parameter you can control which type of statement you want to log (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

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.

like image 100
a_horse_with_no_name Avatar answered Oct 01 '22 14:10

a_horse_with_no_name


I believe OP was actually asking for execution duration, not the timestamp.

To include the duration in the log output, open pgsql/<version>/data/postgresql.conf, find the line that reads

#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

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.

like image 25
Zoltán Avatar answered Oct 01 '22 15:10

Zoltán