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?
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.
SELECT *FROM pg_class;
SELECT *FROM pg_stats;
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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.