POSTGRESQL INTERNALS

Deep dive into query optimization, indexing strategies, and database internals. All measurements from EXPLAIN ANALYZE on the 5.74M row airline 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)

Active indexes

Queries optimized

0.00ms

Fastest query (ms)

Query Performance: Before vs After

Measured with EXPLAIN ANALYZE

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

Index Size Analysis

Sorted by size, with usage statistics

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

PostgreSQL Topics Covered

EXPLAIN Deep Dive

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.