14.2. Statistics Used by the Planner
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
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,
relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values. They are updated by
ANALYZE, and a few DDL commands such as
CREATE INDEX. A
ANALYZE operation that does not scan the entire table (which is commonly the case) will incrementally update the
reltuples count on the basis of the part of the table it did scan, resulting in an approximate value. In any case, 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
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
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, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename = 'road'; attname | inherited | n_distinct | most_common_vals ---------+-----------+------------+------------------------------------ name | f | -0.363388 | I- 580 Ramp+ | | | I- 880 Ramp+ | | | Sp Railroad + | | | I- 580 + | | | I- 680 Ramp name | t | -0.284859 | I- 880 Ramp+ | | | I- 580 Ramp+ | | | I- 680 Ramp+ | | | I- 580 + | | | State Hwy 13 Ramp (2 rows)
Note that two rows are displayed for the same column, one corresponding to the complete inheritance hierarchy starting at the
road table (
t), and another one including only the
road table itself (
The amount of information stored in
ANALYZE, in particular the maximum number of entries in the
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 100 entries. Raising the limit might 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 might be sufficient for columns with simple data distributions.
Further details about the planner's use of statistics can be found in Chapter 67.