Database Reference
In-Depth Information
exclusive time (time consumed by the parent step) and inclusive time (the time of the
parent step plus its child steps).
Figure 9-3. Tabular explain output
Although the HTML table in
Figure 9-3
provides much the same information as our
plain-text output, the color coding and breakout of numbers makes it easier to see where
our estimates are off. For example, yellow, brown, and red highlight areas where you
should focus.
The
rows x
column is the expected number of rows, while the
rows
column shows the
actual number. This reveals that although our final step was expecting 192 records, it
received just one, and the bitmap scan returned 203 false positives caught by the recheck.
Bad row estimates often stem from out-of-date table statistics. It's always a good idea to
run an analysis on tables before a long query to update the statistics.
Gathering Statistics on Statements
The first step in optimizing performance is to determine which queries are bottlenecks.
One monitoring extension useful for getting a handle on your most costly queries is
pg_stat_statements
. This extension provides metrics on running queries: which are the
most frequently run queries and how long each takes. Studying these metrics will help
you determine where you need to focus your query optimization efforts.
pg_stat_statements comes packaged with most PostgreSQL distributions but must be
preloaded on startup to initiate its data-collection process:
1. In
postgresql.conf
, change
shared_preload_libraries = ''
to
shared_pre
load_libraries = 'pg_stat_statements'
.
2. In the customized options section of
postgresql.conf
, add the lines:
