Performance & Concurrency - PostgreSQL 9 Administration - page 238

to find the repeating queries.
F Watch pg_stat_user_tables and pg_statio_user_tables :
F Another way to discover such queries is looking at suspicious behaviors in pg_stat*
tables. Specific things to watch for as are follows:
In the pg_stat_user_tables , fast growth of seq_tup_read means that
there are lots of sequential scans occurring. The ratio of seq_tup_read /
seq_scan shows how many tuples each seqscan reads.
In the pg_statio_user_tables , watch the fields heap_blks_hit and
idx_blks_read , which give you a fairly good idea on how much of your
data is found in PostgreSQL shared buffers ( heap_blks_hit ), and how
much had to be fetched from disk ( idx_blks_read ). If you continuously
see large numbers of blocks being read from disk, you may want to tune
those queries, or if you determine that the disk reads were justified, you can
make the configured shared_buffers value bigger.
Once a suspect is found, make the query slow, so that it is logged
Once you have found out the query that you suspect is slowing you down, you can force
the queries appear in slow query log by locking the any table involved in this query for a
period slightly longer than configured by log_min_duration_statement in
postgresql.conf ile.
A sample psql session from logging all queries accessing mysuspecttable is as follows:
mydb=# begin;
BEGIN
mydb=# lock table mysuspecttable;
LOCK TABLE
select pg_sleep(12);
hannu=# rollback;
ROLLBACK
This also works when you have not found a single suspect query, but have found a table
which is accessed in a suspicious manner, and you want to find out what queries use this table.
Finding slow queries run as prepared statements
If the slow query is not run as is, but is first prepared and then executed, then you need to
be able to connect the PREPARE statement creating the prepared query plan with the actual
invocation of the query using EXECUTE .
This can be done by configuring PostgreSQL to log all queries, and setting the configuration
file parameter log_line_prefix so that it includes either process ID ( '%p' ) or session
ID ( '%c' ). This will help you to trace back matching PREPARE statements if you see a
slow EXECUTE .