Table Statistics

You've seen all the operators that PostgreSQL can use to execute a query. Remember that the goal of the optimizer is to find the plan with the least overall expense. Each operator uses a different algorithm for estimating its cost of execution. The cost estimators need some basic statistical information to make educated estimates.

Table statistics are stored in two places in a PostgreSQL database: pg_class and pg_statistic.

The pg_class system table contains one row for each table defined in your database (it also contains information about views, indexes, and sequences). For any given table, the pg_class.relpages column contains an estimate of the number of 8KB pages required to hold the table. The pg_class.reltuples column contains an estimate of the number of tuples currently contained in each table.

Note that pg_class holds only estimateswhen you create a new table, the relpages estimate is set to 10 pages and reltuples is set to 1,000 tuples. As you INSERT and DELETE rows, PostgreSQL does not maintain the pg_class estimates. You can see this here:

movies=# SELECT * FROM tapes;

 tape_id | title | duration
----------+---------------+----------
 AB-12345 | The Godfather |
 AB-67472 | The Godfather |
 MC-68873 | Casablanca |
 OW-41221 | Citizen Kane |
 AH-54706 | Rear Window |
(5 rows)

movies=# CREATE TABLE tapes2 AS SELECT * FROM tapes;
SELECT
movies=# SELECT reltuples, relpages FROM pg_class
movies-# WHERE relname = 'tapes2';
 reltuples | relpages
-----------+----------
 1000 | 10

Create the tapes2 table by duplicating the tapes table. You know that tapes2 really holds five tuples (and probably requires a single disk page), but PostgreSQL has not updated the initial default estimate.

There are three commands that you can use to update the pg_class estimates: VACUUM, ANALYZE, and CREATE INDEX.

The VACUUM command removes any dead tuples from a table and recomputes the pg_class statistical information:

movies=# VACUUM tapes2;
VACUUM
movies=# SELECT reltuples, relpages FROM pg_class WHERE relname = 'tapes2';
 reltuples | relpages
-----------+----------
 5 | 1
(1 row)

The pg_statistic system table holds detailed information about the data in a table. Like pg_class, pg_statistic is not automatically maintained when you INSERT and DELETE data. The pg_statistic table is not updated by the VACUUM or CREATE INDEX command, but it is updated by the ANALYZE command:

movies=# SELECT staattnum, stawidth, stanullfrac FROM pg_statistic
movies-# WHERE starelid =
movies-# (
movies(# SELECT oid FROM pg_class WHERE relname = 'tapes2'
movies(# );
 staattnum | stawidth | stanullfrac
-----------+----------+-------------
 (0 rows)

movies=# ANALYZE tapes2;
ANALYZE

movies=# SELECT staattnum, stawidth, stanullfrac FROM pg_statistic
movies-# WHERE starelid =
movies-# (
movies(# SELECT oid FROM pg_class WHERE relname = 'tapes2'
movies(# );
 staattnum | stawidth | stanullfrac
-----------+----------+-------------
 1 | 12 | 0
 2 | 15 | 0
 3 | 4 | 0
(3 rows)

PostgreSQL defines a view (called pg_stats) that makes the pg_statistic table a little easier to deal with. Here is what the pg_stats view tells us about the tapes2 table:

movies=# SELECT attname, null_frac, avg_width, n_distinct FROM pg_stats
movies-# WHERE tablename = 'tapes2';

 attname | null_frac | avg_width | n_distinct
---------+-----------+-----------+------------
 tape_id | 0 | 12 | -1
 title | 0 | 15 | -0.8
(2 rows)

You can see that pg_stats (and the underlying pg_statistics table) contains one row for each column in the tapes2 table (except for the duration column where every value happens to be NULL). The null_frac value tells you the percentage of rows where a given column contains NULL. In this case, there are no NULL values in the tapes2 table, so null_frac is set to 0 for each column. avg_width contains the average width (in bytes) of the values in a given column. The n_distinct value tells you how many distinct values are present for a given column. If n_distinct is positive, it indicates the actual number of distinct values. If n_distinct is negative, it indicates the percentage of rows that contain a distinct value. A value of -1 tells you that every row in the table contains a unique value for that column.

pg_stats also contains information about the actual values in a table:

movies=# SELECT attname, most_common_vals, most_common_freqs
movies-# FROM pg_stats
movies-# WHERE tablename = 'tapes2';
 attname | most_common_vals | most_common_freqs
---------+-------------------+-------------------
 tape_id | |
 title | {"The Godfather"} | {0.4}
(2 rows)

The most_common_vals column is an array containing the most common values in a given column. The most_common_freqs value tells you how often each of the most common values appear. By default, ANALYZE stores the 10 most common values (and the frequency of those 10 values). You can increase or decrease the number of common values using the ALTER TABLE ... SET STATISTICS command.

Looking back at the recalls table, you can see that the datea column contains 825 distinct values:

perf=# x
Expanded display is on.
perf=# SELECT
perf-# n_distinct, most_common_vals, most_common_freqs
perf-# FROM
perf-# pg_stats
perf-# WHERE
perf-# tablename = 'recalls' AND attname = 'datea';
-[ RECORD 1 ]-----+-------------------------------------------------------------
n_distinct | 825
most_common_vals | {19791012,19921230,20001129,19980814,19950524,19950901,...}
most_common_freqs | {0.319667,0.005,0.005,0.00466667,0.00433333,0.00433333,...}

(We've turned on psql's expanded display (with the x command) and trimmed the results a bit to make them easier to read.) The most commonly found datea value is 19791012 and it occurs in approximately 32% of all rows. The second most common value is 19921230 and that value is found in .5% of all rows. Go ahead and create an index that covers the datea column and then ANALYZE the recalls table:

perf=# CREATE INDEX recalls_by_datea ON recalls( datea );
CREATE INDEX
perf=# ANALYZE recalls;
ANALYZE

You might expect PostgreSQL to use this index in a query that selects rows based on datea values, and sometimes it does:

perf=# EXPLAIN SELECT * FROM recalls WHERE datea = '19921230';
 QUERY PLAN
------------------------------------------------------------------------
 Index Scan using recalls_by_datea (cost=0.00..31.44 rows=31 width=1908)
 Index Cond: (datea = '19921230'::bpchar)

You can see that the optimizer chose a partial index scan (using the recalls_by_datea index) to satisfy this query. Now try to select a different set of rows:

perf=# EXPLAIN SELECT * FROM recalls WHERE datea = '19791012';
 QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on recalls (cost=0.00..9015.09 rows=10690 width=1908)
 Filter: (datea = '19791012'::bpchar)

In this case, the optimizer thinks it would be faster to perform a complete table scan on the recalls table, ignoring the rows that fail to satisfy the WHERE clause.

The only thing that's changed between the two queries is the value that you're searching for. The recalls table hasn't changed; the statistics haven't changed. Why would PostgreSQL use an index to retrieve the second most-frequently-found value, but not the most-frequently-found value? Because the optimizer knows (based on the pg_stats.most_common_vals and pg_stats.most_common_freqs) that it must process 32% of the recalls table in the second case and reading a table via an index is more expensive than reading it via a sequential scan. To retrieve the second most-frequently-found value, the optimizer knows that it will only read .5% of the table via the index.

Another statistic exposed by pg_stat is called histogram_bounds. The histogram_bounds column contains an array of values for each column in your table. These values are used to partition your data into approximately equally sized chunks. For example, here are the histogram_bounds values for the recalls.potaff column:

perf=# SELECT histogram_bounds FROM pg_stats
perf-# WHERE tablename = 'recalls' and attname = 'potaff';
 histogram_bounds
------------------------------------------------------------
 {3,104,305,700,1503,3203,6503,15263,48003,210003,32000003}

Because there are 11 values shown, the histogram_bounds show that 10% of the potaff values fall between 3 and 104, 10% of the potaff values fall between 104 and 305, 10% fall between 305 and 700, and so on. The optimizer uses the histograms_bounds to decide how much of an index it will need to traverse in order to search for a specific value. For example, if you search for a potaff value of 32000004 (which fits into the last histogram "bucket"), PostgreSQL knows that it will only have to traverse the last 10% of the recalls_by_potaff index to satisfy the query. On the other hand, if you search for the value 210003, PostgreSQL must traverse the last 20% of the index. Here's how the optimizer handles a search over the last 10% of the index:

perf=# EXPLAIN SELECT * FROM recalls WHERE potaff >= 14500003;
 QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using recalls_by_potaff (cost=0.00..17.99 rows=4 width=1908)
 Index Cond: (potaff >= 14500003::numeric)

The optimizer has chosen an index scan to satisfy this query because traversing 10% of the index is less expensive than a complete table scan. Now consider a similar query, but this time, you're searching for a value known to fall within the last 20% of the index:

perf=# EXPLAIN SELECT * FROM recalls WHERE potaff >= 210003;
 QUERY PLAN
----------------------------------------------------------------
 Seq Scan on recalls (cost=0.00..9015.09 rows=3928 width=1908)
 Filter: (potaff >= 210003::numeric)

This time, the optimizer has chosen a table scan. The structure of this query is identical the previous queryonly the search value has changed.

The last statistic stored in pg_stats is an indication of whether the rows in a table are stored in column order:

movies=# SELECT attname, correlation FROM pg_stats
movies-# WHERE tablename = 'tapes2';
 attname | correlation
---------+-------------

 attname | correlation
---------+-------------
 tape_id | 0.7
 title | -0.5
(2 rows)

A correlation of 1 means that the rows are sorted by the given column. In practice, you will see a correlation of 1 only for brand new tables (whose rows happened to be sorted before insertion) or tables that you have reordered using the CLUSTER command.