SUMMARY:

Learn how PostgreSQL statistics, pg_stats, ANALYZE, default_statistics_target, and random_page_cost influence query planning and performance tuning.

Introduction

Have you ever noticed PostgreSQL choosing a sequential scan when an index is available, or estimating far fewer rows than a query actually returns?

These situations can stem from how PostgreSQL understands the data stored in a table. PostgreSQL uses a cost-based query planner, which relies on statistics and cost parameters to choose an execution plan.

In this blog, we will explore how PostgreSQL statistics work, how to read them using pg_stats, what ANALYZE does, and how default_statistics_target and random_page_cost influence query planning.

1. What Are PostgreSQL Statistics?

PostgreSQL collects statistical information about tables and columns to estimate how many rows a query will return.

For example, consider an orders table containing one million records. If 800,000 records belong to one customer and only 100 belong to another, the number of rows returned by a query filtering on customer_id will vary significantly.

PostgreSQL needs to understand this distribution to estimate query selectivity and choose an appropriate execution plan.

Without sufficiently accurate statistics, the planner may underestimate or overestimate the number of rows, potentially leading to inefficient execution plans.

2. What Does ANALYZE Do?

The ANALYZE command collects statistics about table contents and stores them in PostgreSQL’s internal statistics catalogs.

ANALYZE orders;

PostgreSQL typically samples table rows rather than examining every row. The collected statistics include common values, their frequencies, value distribution, and estimated distinct-value counts.

These statistics help the planner estimate how many rows match filters and joins.

PostgreSQL’s autovacuum process can automatically run ANALYZE when sufficient data changes occur. However, after a large data load or significant data distribution change, manually running ANALYZE can help refresh statistics promptly.

Remember that ANALYZE updates statistics; it does not directly improve query performance. The updated statistics may, however, help PostgreSQL select a better execution plan.

3. How to Read pg_stats

The pg_stats view provides a readable representation of collected column statistics.

SELECT
    attname,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders';

Here is what the main columns mean:

  • null_frac: Fraction of rows containing NULL values.
  • n_distinct: Estimated number of distinct values. A negative value represents a fraction of the total row count; for example, -1 indicates approximately all values are distinct.
  • most_common_vals: Values that occur most frequently in the column.
  • most_common_freqs: Frequencies of those common values, expressed as fractions of all rows.
  • histogram_bounds: Histogram boundaries used to estimate selectivity for values not represented by the most-common-values list.

These fields can help identify skewed data distributions and explain why PostgreSQL estimates one filter differently from another.

4. Understanding default_statistics_target

The default_statistics_target parameter controls the default level of detail collected for column statistics.

SHOW default_statistics_target;

Its default value is 100. Increasing it allows PostgreSQL to collect more detailed statistics, generally using a larger sample and potentially recording more common values and histogram boundaries.

For example, if a column has a highly uneven distribution, increasing the statistics target may improve row estimates.

You can change the target for an individual column instead of changing the global default:

ALTER TABLE orders
    ALTER COLUMN customer_id SET STATISTICS 500;

ANALYZE orders (customer_id);

A higher target can improve estimation accuracy but also increases the work required to collect statistics and the storage needed. Adjust it based on observed estimation problems rather than increasing it indiscriminately.

5. Understanding random_page_cost

While statistics help PostgreSQL estimate how many rows a query will return, cost parameters influence how it evaluates alternative execution plans.

One important parameter is random_page_cost:

SHOW random_page_cost;

Its default value is 4.0 in standard PostgreSQL configurations.

This parameter represents the estimated cost of a non-sequential page fetch relative to the planner’s cost scale. It influences the estimated cost of index scans and other operations involving non-sequential access.

A lower value can make index scans more attractive, while a higher value can make sequential scans more attractive. The best setting depends on the storage system, caching, and workload.

For SSD-backed systems, a lower value may be appropriate, but changing it blindly can produce worse plans. Always validate changes against representative queries.

Conclusion

Understanding PostgreSQL statistics is essential for effective query troubleshooting. ANALYZE collects statistics, pg_stats helps you inspect them, default_statistics_target controls their level of detail, and random_page_cost influences how the planner compares execution strategies.

When investigating a slow query, start by examining its execution plan and comparing estimated rows with actual rows using EXPLAIN (ANALYZE, BUFFERS). Inspect the relevant column statistics before changing planner settings.

The goal is not to force PostgreSQL to use an index. It helps PostgreSQL estimate the data accurately and choose the most efficient execution plan for the workload.

Need help diagnosing slow queries or tuning PostgreSQL performance? Talk to Virtual-DBA’s PostgreSQL experts.