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