Indexes and Performance Tradeoffs

MediumSQL · PostgreSQL

Problem

What are indexes, and how do they impact write performance versus read performance in a database used for clinical data at Medpace?

For the hands-on portion, use the supplied index statistics to classify indexes according to their observed read activity and write-maintenance operations.

Output

One row per index on clinical_records
Return index_name, read_hit_ratio, maintenance_operations, and performance_assessment
Sort by performance_assessment priority, then index_name alphabetically

Output details

Calculate read_hit_ratio as idx_tup_fetch / idx_tup_read, rounded to two decimal places, and use 0 when idx_tup_read is zero.
Calculate maintenance_operations as inserts + updates + deletes.
Classify zero scans with positive maintenance as unused_write_cost, scans at least 1000 with maintenance at most 1000 as read_favorable, and scans at least 100 with maintenance above 1000 as mixed_tradeoff; classify all others as low_activity.
Order assessment priorities as unused_write_cost, read_favorable, mixed_tradeoff, then low_activity, with index_name ascending within each priority.

Schema

index_usage_stats

Column	Type	Description
index_namePK	VARCHAR(100)	Name of the database index
table_name	VARCHAR(100)	Table associated with the index
idx_scan	BIGINT	Number of index scans
idx_tup_read	BIGINT	Index entries returned by scans
idx_tup_fetch	BIGINT	Table rows fetched through the index
inserts	BIGINT	Rows inserted during the observation period
updates	BIGINT	Rows updated during the observation period
deletes	BIGINT	Rows deleted during the observation period

Tablesindex_usage_stats