How can I optimize the query by it’s plan?

by

Summary

The given query is a complex join operation involving three tables: bookings.seats, bookings.routes, and bookings.flights. The query plan indicates a Hash Join with a high cost, suggesting potential optimization opportunities. To optimize the query, we need to analyze the query plan, table statistics, and index usage.

Root Cause

The root cause of the query’s poor performance can be attributed to:

  • Inefficient join order: The query plan shows a Nested Loop join, which can be slower than other join methods.
  • Lack of effective indexing: The Index Only Scan on bookings.seats is limited by the seat_no filter, which may not be selective enough.
  • Insufficient table statistics: The table statistics may not accurately reflect the data distribution, leading to suboptimal query planning.

Why This Happens in Real Systems

This type of query performance issue can occur in real systems due to:

  • Poor database design: Inadequate indexing, improper table structure, or insufficient statistics can lead to suboptimal query plans.
  • Data distribution: Skewed data distribution or unexpected data patterns can cause the query optimizer to make poor choices.
  • System configuration: Inadequate system resources, incorrect configuration settings, or outdated software can contribute to performance issues.

Real-World Impact

The impact of this query performance issue can be significant, including:

  • Slow query execution: Long query execution times can lead to frustrated users, delayed reports, and decreased productivity.
  • Increased resource usage: Inefficient queries can consume excessive system resources, causing other queries to slow down or even leading to system crashes.
  • Decreased scalability: Poorly performing queries can limit the system’s ability to handle increased workload or user growth.

Example or Code

To optimize the query, we can try the following:

CREATE INDEX idx_seats_airplane_code ON bookings.seats (airplane_code);
CREATE INDEX idx_routes_airplane_code ON bookings.routes (airplane_code);
CREATE INDEX idx_flights_route_no ON bookings.flights (route_no);

ANALYZE bookings.seats;
ANALYZE bookings.routes;
ANALYZE bookings.flights;

EXPLAIN (ANALYZE) SELECT f.actual_departure, f.actual_arrival, f.route_no
FROM bookings.seats s
INNER JOIN bookings.routes r ON r.airplane_code = s.airplane_code
INNER JOIN bookings.flights f ON f.route_no = r.route_no
WHERE s.seat_no = '4A';

How Senior Engineers Fix It

Senior engineers can fix this issue by:

  • Analyzing the query plan: Understanding the query plan and identifying bottlenecks.
  • Creating effective indexes: Designing and creating indexes that support the query’s filter and join conditions.
  • Gathering accurate statistics: Ensuring that table statistics are up-to-date and accurate to support optimal query planning.
  • Testing and iterating: Continuously testing and refining the query and indexing strategy to achieve optimal performance.

Why Juniors Miss It

Junior engineers may miss this issue due to:

  • Lack of experience: Limited experience with complex queries, indexing, and query optimization.
  • Insufficient knowledge: Limited understanding of database internals, query planning, and performance optimization techniques.
  • Overreliance on automation: Relying too heavily on automated tools and not taking the time to manually analyze and optimize queries.

Leave a Comment