Collecting Index and Table Usage Statistics

Imagine your database is a huge warehouse. Indexes are like catalogs and lists that help you quickly find what you need. Tables are the actual goods on the shelves. If an index isn’t used much, it’s like a catalog gathering dust in the far corner. If a table is heavily used but has a bad structure or too much extra data, it can stress out your warehouse (the database) and slow everything down.

Main Goals of Analysis:

1. Evaluating index usage efficiency. For example, is your fancy index just sitting there doing nothing? Toss it!
2. Figuring out read and write operation frequency. Helps you see which tables are getting the most action.
3. Query optimization. Stats help you spot where you can speed things up by adding or tweaking indexes.

The pg_stat_user_indexes and pg_stat_user_tables Views

PostgreSQL has two super useful views for collecting stats: pg_stat_user_indexes and pg_stat_user_tables. Let’s break them down.

pg_stat_user_indexes: How Are Indexes Used?

Main fields:

• relname — the name of the table the index belongs to.
• indexrelname — the name of the index.
• idx_scan — how many times the index was used for searching.
• idx_tup_read — number of rows read using the index.
• idx_tup_fetch — number of actual rows returned (after filters).

Sample query:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Here’s what’s going on:

• We sort by the number of index calls (idx_scan) to see which indexes are the most popular.
• If an index is barely used (idx_scan = 0), maybe you don’t need it at all?

Practical use:

You roll out a new app version and add a new index. With pg_stat_user_indexes, you can check if your query actually started using the new index, or if PostgreSQL is still ignoring your optimization masterpiece and going the old route.

pg_stat_user_tables: Checking Out Table Data

Main fields:

• relname — table name.
• seq_scan — number of sequential scans (no indexes used).
• seq_tup_read — number of rows returned from the table during sequential scans.
• idx_scan — number of index scans for the table.
• n_tup_ins — number of inserted rows.
• n_tup_upd — number of updated rows.
• n_tup_del — number of deleted rows.

Sample query:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

What do we see here?

• Tables with lots of sequential scans (seq_scan) might need an index.
• The number of inserts, updates, and deletes helps you see how often the table’s data changes.

Practical use: You’re working with the users table, which stores all your app’s user data. With pg_stat_user_tables, you notice that sequential scans (seq_scan) for this table are through the roof. That’s a hint: time to create indexes on the most-used columns to speed up your queries.

Example: Analyzing Indexes and Tables in a Real Database

Let’s say you have a database with orders and products tables. You want to see how efficiently tables and indexes are being used.

Index analysis:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

You see that the orders_customer_id_idx index was called 50,000 times, but orders_date_idx only 5 times. Maybe you don’t need orders_date_idx at all.

Table analysis:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

The products table is constantly getting sequential scans. That’s a sign: your product catalog needs more indexes.

Common Mistakes and How to Avoid Them

The classic newbie trap is ignoring the stats. For example, you add a new index thinking, “Now my queries will fly!” but PostgreSQL doesn’t use it because the stats weren’t updated automatically. After big changes in your tables, don’t forget to update stats manually with the ANALYZE command.

Another common mistake is going overboard with indexes. Remember, every index takes up disk space and slows down inserts, updates, and deletes. Use pg_stat_user_indexes stats to make sure your index is actually useful and not just dead weight.

Why Does This Knowledge Matter?

In real-world development: if your database is slow, the first thing you’ll check is tables and indexes.

In interviews: questions about index optimization are a classic SQL interview move. Can you explain pg_stat_user_indexes? You’re already halfway there.

In database administration: monitoring is a daily DBA routine. Without stats on tables and indexes, you can’t improve anything.

2

Task

SQL SELF, level 46, lesson 3

Locked

Viewing Unused Indexes

Viewing Unused Indexes

Open