As we saw in the previous section, the query planner needs to estimate the number of rows retrieved by a query in order to make good choices of query plans. This section provides a quick look at the statistics that the system uses for these estimates.
One component of the statistics is the total number of entries in
each table and index, as well as the number of disk blocks occupied
by each table and index. This information is kept in the table
pg_class
, in
the columns reltuples
and
relpages
. We can look at it with
queries similar to this one:
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%'; relname | relkind | reltuples | relpages ----------------------+---------+-----------+---------- tenk1 | r | 10000 | 358 tenk1_hundred | i | 10000 | 30 tenk1_thous_tenthous | i | 10000 | 30 tenk1_unique1 | i | 10000 | 30 tenk1_unique2 | i | 10000 | 30 (5 rows)
Here we can see that tenk1
contains 10000
rows, as do its indexes, but the indexes are (unsurprisingly) much
smaller than the table.
For efficiency reasons, reltuples
and relpages
are not updated on-the-fly,
and so they usually contain somewhat out-of-date values.
They are updated by VACUUM
, ANALYZE
, and a
few DDL commands such as CREATE INDEX
. A stand-alone
ANALYZE
, that is one not part of VACUUM
,
generates an approximate reltuples
value
since it does not read every row of the table. The planner
will scale the values it finds in pg_class
to match the current physical table size, thus obtaining a closer
approximation.
Most queries retrieve only a fraction of the rows in a table, due
to having WHERE
clauses that restrict the rows to be
examined. The planner thus needs to make an estimate of the
selectivity of WHERE
clauses, that is,
the fraction of rows that match each condition in the
WHERE
clause. The information used for this task is
stored in the pg_statistic
system catalog. Entries in pg_statistic
are updated by the ANALYZE
and VACUUM
ANALYZE
commands, and are always approximate even when freshly
updated.
Rather than look at pg_statistic
directly,
it's better to look at its view pg_stats
when examining the statistics manually. pg_stats
is designed to be more easily readable. Furthermore,
pg_stats
is readable by all, whereas
pg_statistic
is only readable by a superuser.
(This prevents unprivileged users from learning something about
the contents of other people's tables from the statistics. The
pg_stats
view is restricted to show only
rows about tables that the current user can read.)
For example, we might do:
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'road'; attname | n_distinct | most_common_vals ---------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- name | -0.467008 | {"I- 580 Ramp","I- 880 Ramp","Sp Railroad ","I- 580 ","I- 680 Ramp","I- 80 Ramp","14th St ","5th St ","Mission Blvd","I- 880 "} thepath | 20 | {"[(-122.089,37.71),(-122.0886,37.711)]"} (2 rows)
pg_stats
is described in detail in
Section 43.46, “pg_stats
”.
The amount of information stored in pg_statistic
,
in particular the maximum number of entries in the
most_common_vals
and histogram_bounds
arrays for each column, can be set on a
column-by-column basis using the ALTER TABLE SET STATISTICS
command, or globally by setting the
default_statistics_target configuration variable.
The default limit is presently 10 entries. Raising the limit
may allow more accurate planner estimates to be made, particularly for
columns with irregular data distributions, at the price of consuming
more space in pg_statistic
and slightly more
time to compute the estimates. Conversely, a lower limit may be
appropriate for columns with simple data distributions.