Analyzing index usage with pg_stat in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the pg_stat_user_indexes view.

PostgreSQL

SELECT [1] FROM pg_stat_user_indexes;

Drag options to blanks, or click blank then click option'

Aschemaname

Bindexrelname

C*

Drelid

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column when all columns are needed.

Using an invalid column name.

The * symbol selects all columns from the table or view.

2fill in blank

medium

Complete the code to filter index usage statistics for the table named 'employees'.

PostgreSQL

SELECT * FROM pg_stat_user_indexes WHERE relname = [1];

Drag options to blanks, or click blank then click option'

A"employees"

B'employees'

Cemployees

D'employee'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around the table name.

Using the wrong table name.

Table names in SQL strings must be enclosed in single quotes.

3fill in blank

hard

Fix the error in the code to correctly count index scans for all user indexes.

PostgreSQL

SELECT indexrelname, [1] FROM pg_stat_user_indexes;

Drag options to blanks, or click blank then click option'

Aidx_scan

Bsum(idx_scan)

Ccount(idx_scan)

Davg(idx_scan)

Attempts:

3 left

💡 Hint

Common Mistakes

Using aggregation functions incorrectly.

Trying to count a column that already holds counts.

The idx_scan column already contains the count of index scans, so no aggregation is needed.

4fill in blank

hard

Fill both blanks to select index names and their scan counts, filtering for indexes with more than 100 scans.

PostgreSQL

SELECT [1], [2] FROM pg_stat_user_indexes WHERE idx_scan > 100;

Drag options to blanks, or click blank then click option'

Aindexrelname

Bidx_scan

Crelname

Dschemaname

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting wrong columns like table name instead of index name.

Not filtering correctly on scan counts.

indexrelname is the index name, and idx_scan is the number of times the index was scanned.

5fill in blank

hard

Fill all three blanks to select index names, table names, and scan counts for indexes scanned more than 50 times in the 'public' schema.

PostgreSQL

SELECT [1], [2], [3] FROM pg_stat_user_indexes WHERE idx_scan > 50 AND schemaname = 'public';

Drag options to blanks, or click blank then click option'

Aindexrelname

Brelname

Cidx_scan

Dschemaname

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the schema name instead of table name.

Using incorrect filter conditions.

This query shows the index name (indexrelname), the table name (relname), and the number of scans (idx_scan) for indexes in the 'public' schema scanned more than 50 times.