PostgreSQL: Important Statistics Table, Used by the Query Planner

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In any RDBMS system, Different type of Statistics is very important for Query Optimizer and Planner to choose and generate the best Query Execution Plan.

PostgreSQL Database Server also managing different types of Statistic which are used by the Query Optimizer to generate the best Query Execution Plan.

Statistical data should be updated, Why?
For Example,

We have one table with 100000 rows, but table statistic data are not updated and showing only 1000 tuples information.

Query optimizer always looks into Statistic tables, It never looks into direct table files.

So in this situation, It generates the wrong Query Execution Plan because It has only 1000 rows information, but actually there are 100000 rows.

PostgreSQL Database Server is mainly using two Statistical Table.

You can also query these tables and can find information about Tables, Indexes, Pages and others.

When you look into table pg_class, you can find “reltuples” column. You can compare this column value with your actual table record count.

If you find any mismatch, for that table statistic information is not updated.

Execute Analyze command:

When you find that Statistical information is not updated, you can execute the ANALYZE command on the Database or Table.
After executing the ANALYZE command, It updates all require statistics.

PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE