Generating planner statistics

In this recipe, we will be discussing how we can use PostgreSQL to generate statistics.

Getting ready

Database statistical information plays a crucial role in deciding the proper execution plan for the given SQL statement. PostgreSQL provides a utility command called ANALYZE, which collects statistics from tables and makes them available to the planner. PostgreSQL also provides another utility background process called autovacuum, which does a similar job to analyze. All these collected statistics will be stored into the PostgreSQL catalog tables.

How to do it...

Now, for demonstration let's create a test table and populate a few entries in it:

postgres=# CREATE TABLE test(t INT); 
CREATE TABLE 
postgres=# SELECT COUNT(*) FROM pg_stats WHERE tablename = 'test'; 
 count 
------- 
     0 
(1 row) 
postgres=# INSERT INTO test VALUES(generate_series(1, 1000));      
INSERT 0 1000 
postgres=# ANALYZE test; 
ANALYZE &...