Statistics attribute in PostgreSQL

The statistics attribute of a column in PostgreSQL is used to control the details of the column analysis during analysis, which affects the Execution Plan Selection. For details, see the document. By default, the statistics values of all columns in pgsql are 100, which is a fairly conservative number. That is, the analysis of different values in the column is rough. Unevenly distributed column values

The statistics attribute of a column in PostgreSQL is used to control the details of the column analysis during analysis, which affects the Execution Plan Selection. For details, see the document. By default, the statistics values of all columns in pgsql are 100, which is a fairly conservative number. That is, the analysis of different values in the column is rough. Unevenly distributed column values

Statistics attribute in PostgreSQL

This attribute is used to control the detailed analysis of Columns during analysis, which affects the selection of execution plans. For details, see the document. By default, the statistics values of all columns in pgsql are 100, which is a fairly conservative number. That is, the analysis of different values in the column is rough. When the column value distribution is uneven, it is often seen that the execution plan uses a rough average to estimate and selects an unoptimal path. Www.2cto.com

By increasing this parameter, many problems with incorrect execution plans can be solved. In particular, it is necessary to increase the default parameters for some frequently used query columns. I feel that it is necessary to increase to 1000. According to the official documentation, the only disadvantage of increasing this parameter is that analyze takes a longer time. From the actual situation, the increase from 100 to 1000 basically does not feel the difference in analyze time.

How can we know that the statistics of a column needs to be improved? The most convenient is to use explain analyze. If the estimation value (row) of the execution result of a step in the execution plan is very different from the actual obtained value (actual), it is necessary to increase this parameter. How can this problem be improved?

Alter table [table name] alter column [column name] set statistics = 1000;

After the upgrade, remember to re-analyze the table.

Another thing to note is that the estimation of the column values in the index is inaccurate (depending on the execution plan). At this time, you need to adjust the statistics of the columns in the index. The method is the same as that of adjusting the table, consider indexes as tables:

Alter table [index name] alter column [column name] set statistics = 1000;