What Does pg_statistic Tell Us? PostgreSQL Internals Explained

PostgreSQL uses statistics to determine the plans it has chosen. These statistics are from the manual ANALYZE or autovacuum running ANALYZE, and you can also create your own extended statistics using the CREATE STATISTICS command; even though those are stored separately, in pg_statistic_ext and pg_statistic_ext_data, not in pg_statistic. That is not what we are discussing in this post. We will cover what the pg_statistic is, the internal catalog table that PostgreSQL manages.

What does pg_statistic have internally?

You may simply run the query: SELECT * from pg_statistic LIMIT 1;

One thing to note before you run it: pg_statistic is readable only by superusers.

With expanded display ON, this is what I got:

starelid    | 1255
staattnum   | 3
stainherit  | f
stanullfrac | 0
stawidth    | 4
stadistinct | 6
stakind1    | 1
stakind2    | 3
stakind3    | 0
stakind4    | 0
stakind5    | 0
staop1      | 607
staop2      | 609
staop3      | 0
staop4      | 0
staop5      | 0
stacoll1    | 0
stacoll2    | 0
stacoll3    | 0
stacoll4    | 0
stacoll5    | 0
stanumbers1 | {0.93730587,0.055351596,0.0031064672,0.0019768428,0.0019768428}
stanumbers2 | {0.98767483}
stanumbers3 | 
stanumbers4 | 
stanumbers5 | 
stavalues1  | {11,2200,13299,1244946,1278930}
stavalues2  | 
stavalues3  | 
stavalues4  | 
stavalues5  | 

Let’s see what each column in this catalog table tells us.

1. starelid :

This is the oid of the table these statistics belong to. Here, we have 1255, which means the corresponding record contains the statistics of the table with oid 1255. You can see the table name by running the query below:

SELECT '1255'::regclass;
-- Or
SELECT oid, relname
FROM pg_class
WHERE oid = 1255;

The output was:

-[ RECORD 1 ]----
oid     | 1255
relname | pg_proc

2. staattnum:

This denotes which column’s statistics are given in the record. Here, we have a value of staattnum = 3, which means we have stats of the 3rd column of table 1255 in this record.You can run:

postgres=# SELECT attnum, attname
FROM pg_attribute
WHERE attrelid = 1255
  AND attnum = 3;

The output is:

-[ RECORD 1 ]---------
attnum  | 3
attname | pronamespace

Which means this record is the stats of column pronamespace of table pg_proc.

3. stainherit :

We have stainherit = f. It means these are normal statistics for the table itself, and not stats calculated for inherited or partitioned tables. When stainherit = t, the row describes the table together with all of its inheritance or partition children, and both rows can exist for the same column at the same time. This matters more than it first appears: a partitioned table's parent holds no rows of its own, so for partitioned tables it is the stainherit = t row that the planner actually uses.

4. stanullfrac :

This value shows the fraction of NULL values in the column. Since here we have stanullfrac = 0, it means that ANALYZE says that there are no NULL values. Keep in mind that these numbers come from a sample and not a full table scan, so a 0 does not by itself prove the column contains no NULLs at all — on a large table a handful of NULLs can easily be missed.

5. stawidth :

Refers to the average width of the values in bytes. It is an integer column, and here we have stawidth = 4, which means 4 bytes. If a value has been pushed out of line, only the size of the pointer is counted here, not the size of the real datum. For a fixed-width type, the number is simply the type length, which is the case in our example; pronamespace is an oid, so stawidth = 4.

6. stadistinct :

This value tells the number of distinct values in the given column, but it is read differently depending on its sign:

Values

The negative form exists for a good reason. The row count in pg_class is updated more often than pg_statistic is, so storing a ratio lets the estimate stay meaningful as the table grows, where a fixed number would quietly go stale. For something like a boolean column, on the other hand, a fixed count is the correct thing to store.

Here we have stadistinct = 6, so pg_proc.pronamespace is estimated to hold 6 distinct values. Note the word estimated; this comes from ANALYZE's sample, not from a COUNT(DISTINCT).

7. stakind1:

This is the important part we have to understand. PostgreSQL does not hard-wire a fixed meaning to each slot. But there are five generic slots, and each stakindN says what kind of statistic slot N happens to hold. The defined kinds below;

Here, we have stakind1 = 1, where 1 means STATISTIC_KIND_MCV and STATISTIC_KIND_MCV (MCV means most common value), and stakind1 = 1 means that slot 1 has MCV statistics of this column. The details of the most common values will appear there.

And the data for this slot is :

stanumbers1 | {0.93730587,0.055351596,0.0031064672,0.0019768428,0.0019768428}

stavalues1 | {11,2200,13299,1244946,1278930}

Note that these two arrays match by position, that is:

So this tells how much frequency each value has.

8. stakind2 :

For which we have value 3, that means correlation; for that we have data as stanumbers2 | {0.98767483}, that is approximately 1. Correlation tells postgresql whether the values in this column physically ordered in the same order as their logical values?”

And their values can range from -1…0…1.

So PostgreSQL has found that as it walks through the physical table, the values are ordered almost perfectly. This is important mainly for index scan cost estimation.

9. What is staop1 and staop2: 

They represent the OIDs of operators used with the statistics.

For example, PostgreSQL needs to know which operators the statistics correspond to. What the operator means depends on that slot's kind: for an MCV slot, it is the = operator used to decide whether two values are the same, while for a histogram or correlation slot it is the < operator that defines the sort ordering.

You can inspect them:

SELECT oid, oprname
FROM pg_operator
WHERE oid IN (607, 609);

These internal details are usually not something you need to worry about while learning the main statistics flow.

10. And the remaining values:

stakind3 = 0

stakind4 = 0

stakind5 = 0

They simply say that no statistic stored in these slots. In this case, postgresql only needed two slots to keep the stats of this column, so the others are empty.

pg_statistic may look like an internal catalog filled with cryptic columns, but each value plays a very important role in helping postgres understand the data it is querying. From NULL fractions and distinct value estimates to most common values and correlation, these statistics directly influence the planner's decisions.