Database Reference
In-Depth Information
pg_stat_statements.max = 10000
pg_stat_statements.track = all
3. Restart your
postgresql
service.
4. In any database you want to use for monitoring, enter
CREATE EXTENSION
pg_stat_statements;
.
The extension provides two key features:
• A view called
pg_stat_statements
, which shows all the databases to which the
currently connected user has access.
• A function called
pg_stat_statements_reset
, which flushes the query log. This
function can be run only by superusers.
The query in
Example 9-7
lists the top five most costly queries in the
post
gresql_book
database.
Example 9-7. Expensive queries in specific database
SELECT
query
,
calls
,
total_time
,
rows
,
100
.
0
*
shared_blks_hit
/
nullif
(
shared_blks_hit
+
shared_blks_read
,
0
)
AS
hit_percent
FROM
pg_stat_statements
As
s
INNER
JOIN
pg_database
As
d
On
d
.
oid
=
s
.
dbid
WHERE
d
.
datname
=
'postgresql_book'
ORDER
BY
total_time
DESC
LIMIT
5
;
Guiding the Query Planner
The planner's behavior is driven by the presence of indexes, cost settings, strategy set‐
tings, and its general perception of the distribution of data. In this section, we'll go over
various approaches for optimizing the planner's behavior.
Strategy Settings
Although the PostgreSQL query planner doesn't accept index hints as some other da‐
tabase products do, you can disable various strategy settings on a per-query or perma‐
nent basis to dissuade the planner from going down an unproductive path. All planner
optimizing settings are documented in the section
Planner Method Configuration
of
the manual. By default, all strategy settings are enabled, arming the planner for maxi‐
mum flexibility. You can disable various strategies if you have some prior knowledge of
the data. Keep in mind that disabling doesn't necessarily mean that the planner will be
barred from using the strategy. You're only making a polite request to the planner to
avoid it.
Two settings that we occasionally disable are the
enable_nestloop
and
enable_seqs
can
. The reason is that these two strategies tend to be the slowest and should be used
