Logging Difficult Queries

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Added pgsi, pg_stat_statements)
 
Line 1: Line 1:
 
{{Languages}}
 
{{Languages}}
If you want to find the queries that are taking the longest on your system, you can do that by setting [http://www.postgresql.org/docs/current/static/runtime-config-logging.html log_min_duration_statement] to a positive value representing how many milliseconds the query has to run before it's logged.  Starting in PostgreSQL 8.4, you can use [http://www.postgresql.org/docs/8.4/static/pgstatstatements.html pg_stat_statements] for this purpose as well, without needing an external utility.
+
If you want to find the queries that are taking the longest on your system, you can do that by setting [http://www.postgresql.org/docs/current/static/runtime-config-logging.html log_min_duration_statement] to a positive value representing how many milliseconds the query has to run before it's logged.  In PostgreSQL 8.4+, you can use [http://www.postgresql.org/docs/current/static/pgstatstatements.html pg_stat_statements] for this purpose as well, without needing an external utility.
  
 
Some utilities that can help sort through this data are:
 
Some utilities that can help sort through this data are:

Latest revision as of 16:48, 21 March 2013

If you want to find the queries that are taking the longest on your system, you can do that by setting log_min_duration_statement to a positive value representing how many milliseconds the query has to run before it's logged. In PostgreSQL 8.4+, you can use pg_stat_statements for this purpose as well, without needing an external utility.

Some utilities that can help sort through this data are:

If you are using these tools, you might even consider a period where set the minimum duration to 0 and therefore get all statements logged. This is will be intensive on the logging side, but running that data through one of the tools will give you a lot of insight into what your server is doing.

One thing that can cause queries to pause for several seconds is a checkpoint. If you periodically see many queries all taking several seconds all finishing around the same time, consider Logging Checkpoints and seeing if those times line up, and if so tune appropriately.

[edit] auto-explain

In PostgreSQL, the Auto-Explain contrib module allows saving explain plans only for queries that exceed some time threshold. Seeing the bad plans can help determine why queries are slow, instead of just that they are slow. See Waiting for 8.4 - auto-explain for an example.

Personal tools