In this recipe, we will be discussing how we can use PostgreSQL to generate statistics.
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.
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(*) ...