ANALYZE for statistics collection in PostgreSQL - Mini Project: Build & Apply

Using ANALYZE to Collect Table Statistics in PostgreSQL

📖 Scenario: You are a database administrator for a small online bookstore. You want to help PostgreSQL make better decisions when running queries by collecting up-to-date statistics about your book sales data.

🎯 Goal: Learn how to use the ANALYZE command in PostgreSQL to collect statistics on a table, which helps the database optimize query performance.

📋 What You'll Learn

Create a table named sales with columns id, book_title, and quantity.

Insert sample data into the sales table.

Use the ANALYZE command to collect statistics on the sales table.

Verify that statistics have been collected by querying the system catalog.

💡 Why This Matters

🌍 Real World

Database administrators regularly run ANALYZE to keep query performance fast by updating statistics.

💼 Career

Knowing how to collect and check statistics is essential for roles like database administrator and backend developer.

1

Create the sales table

Create a table called sales with these columns: id as an integer primary key, book_title as text, and quantity as integer.

PostgreSQL

-- Your code here to create the sales table

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sample data into sales

Insert these three rows into the sales table: (1, 'The Great Gatsby', 5), (2, '1984', 8), and (3, 'To Kill a Mockingbird', 3).

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    book_title TEXT,
    quantity INTEGER
);
-- Your code here to insert sample data

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Run ANALYZE on the sales table

Write the SQL command to run ANALYZE on the sales table to collect statistics.

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    book_title TEXT,
    quantity INTEGER
);

INSERT INTO sales (id, book_title, quantity) VALUES
(1, 'The Great Gatsby', 5),
(2, '1984', 8),
(3, 'To Kill a Mockingbird', 3);
-- Your code here to run ANALYZE on sales

Need a hint?

Use the ANALYZE command followed by the table name.

4

Check collected statistics in pg_stat_all_tables

Write a query to select relname and last_analyze from pg_stat_all_tables where relname is 'sales'.

PostgreSQL

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    book_title TEXT,
    quantity INTEGER
);

INSERT INTO sales (id, book_title, quantity) VALUES
(1, 'The Great Gatsby', 5),
(2, '1984', 8),
(3, 'To Kill a Mockingbird', 3);

ANALYZE sales;
-- Your code here to query pg_stat_all_tables for sales statistics

Need a hint?

Use a SELECT statement with a WHERE clause filtering on relname.