PostgreSQL High Performance Cookbook

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(*) ...