QUERY PERFORMANCE

Query optimization, indexing strategy and storage behaviour behind the dashboard. Every measurement is taken with EXPLAIN ANALYZE against the live dataset.

A revenue lookup that took 381ms now returns in 0.13ms. That is a 3,024x improvement from a single composite index. The dashboard query that joins flights, airports, and aggregates delay rates dropped from 147ms to 0.14ms using a materialized view with targeted indexes. Below you can trace each optimization: what the query planner chose before, what it chooses after, and how much storage each index costs. Twenty indexes consume 300+ MB across the database, but two of them (boarding_passes_pkey at 73 MB) have never been used.

Best speedup (mat view)

dashboard query, 147ms to 0.14ms

Active indexes

300+ MB of B-tree and GIN storage

Queries optimized

each measured with EXPLAIN ANALYZE

0.00ms

Fastest query (ms)

materialized view read

Query Performance: Before vs After

Measured with EXPLAIN ANALYZE

Not every attempt pays off, and that is part of the story: route delay analysis barely moved (1x) because its bottleneck is the aggregation, not the lookup.

Flights from SVO with status Arrived44x

BEFORE

32ms

AFTER

0.71ms

Route delay analysis1x

BEFORE

108ms

AFTER

91ms

Revenue for specific flight3024x

BEFORE

381ms

AFTER

0.13ms

Dashboard query (mat view)1038x

BEFORE

147ms

AFTER

0.14ms

Both bars share one linear scale. Some optimized times are so small the bar sits at minimum width — read the numbers.

Index Size Analysis

Sorted by size, with usage statistics

Zero in the Scans column means storage with no payback: boarding_passes_pkey alone holds 73 MB that no query has ever read.

Index	Size	Scans	Tuples read
ticket_flights_pkey	91 MB	1,894,298	1,894,366
boarding_passes_pkey	73 MB	0	0
boarding_passes_flight_id_boarding_no_key	41 MB	0	0
boarding_passes_flight_id_seat_no_key	41 MB	147,731	5,668,343
tickets_pkey	25 MB	3	829,073
idx_tickets_book_ref	16 MB	4,493	2,499,173
idx_tf_flight_id	16 MB	201,440	12,566,905
bookings_pkey	13 MB	11	593,443
flights_flight_no_scheduled_departure_key	2048 kB	2	82,518
flights_pkey	1456 kB	106	132,439
idx_flights_sched_dep	928 kB	0	0
idx_flights_dep_status	488 kB	62	262,714
idx_flights_arrived	360 kB	129	1,011,292
seats_pkey	48 kB	0	0
mv_route_delay_summary_delay_pct_idx	32 kB	1	38
mv_daily_revenue_flight_date_fare_class_idx	32 kB	0	0
mv_route_delay_summary_departure_airport_arrival_airport_idx	32 kB	0	0
mv_aircraft_utilization_aircraft_code_idx	16 kB	0	0
aircrafts_pkey	16 kB	4	36
mv_daily_revenue_flight_date_idx	16 kB	0	0

Scans = times the planner chose the index since statistics were reset. Showing 20 / 21. Click a column header to sort.

Optimization Areas

Query Plan Analysis

Seq Scan, Index Scan, Index-Only Scan, Bitmap Scan. Hash Join, Nested Loop, Merge Join. Cost model and actual vs estimated rows.

Index Strategies

Composite B-tree, partial indexes, expression indexes, GIN for JSONB, covering indexes with INCLUDE. 4,780x speedup on JSONB search.

Table Partitioning

Range partitioning by month. Partition pruning eliminates 80% of scans. Trade-off: point lookups 5x slower.

Statistics & Monitoring

pg_stat_user_tables, pg_stat_user_indexes, cache hit ratios. 93-100% cache hit across tables.

VACUUM Tuning

Dead tuple lifecycle, VACUUM vs VACUUM FULL, autovacuum thresholds. XID wraparound prevention.

WAL & Checkpoints

Write-Ahead Log config, checkpoint statistics, synchronous commit trade-offs. Cloud SQL constraints.

← BACK

FLEET

PIPELINE