PG_STATISTIC Sistem Tablosu

Planner

Açıklaması şöyle

PostgreSQL doesn't know how fast your query will be. It makes an educated guess.

The query planner has statistics about the data and a cost model shaped by settings like random_page_cost, seq_page_cost, cpu_tuple_cost and effective_cache_size.

Then there is the actual machine underneath it: storage latency, available memory, CPU performance, filesystem cache, shared buffers, other workloads competing for the same resources.

The planner doesn't know all of that. It has a model.

Most of the time the model is good enough. Problems start when the distance between that model and reality becomes too large.

I've seen databases where the configuration was perfectly reasonable when it was created. Years later, the database was much larger, the workload had changed, and the storage underneath was completely different. Nobody had touched the settings.

Or a filter on two correlated columns is estimated at 1,000 rows but returns 1,000,000.

Partitioning helps here for a reason people rarely mention: each partition gets its own statistics. A 2026 partition isn't described by the shape of ten years of history.

Then there is caching. A plan that looks expensive on paper can be cheap because the pages are already in memory. The reverse happens just as easily.

Each individual input can look reasonable. The combination can still be wrong.

That's why I don't find "PostgreSQL chose a bad plan" a useful diagnosis.

I'd rather ask: what did the planner believe about the data and the cost of accessing it, and how far was that from reality?

Basic Statistics

Açıklaması şöyle

Statistics are data collected by Postgres used to inform its selection of query plans. Out of the box, Postgres samples the possible values for each column of each table to create histograms and a list of the most common values (among other things). These are used to estimate how many rows will result from applying some set of filters to a table.

For larger tables, the planner can’t keep track of every single value a column holds. Instead, it samples the values of each column and uses those to make estimations. We can tweak how much sampling Postgres does for each column on each table with

ALTER TABLE table ALTER column SET STATISTICS {-1 ..10000}

where -1 sets it to the default value of 100 (docs). This number sets how many buckets are used in the histogram and how many of the most common values are stored.

The downsides to increasing the statistics for a column are that more data must be stored in pg_statistic and running ANALYZE on the column's table takes longer.