Optimizing Query Performance Flashcards
7 cards from real Data Engineering practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Optimizing Query Performance flashcards as text
A correlated subquery executed once per outer row is best optimized by:
Answer: Rewriting it as a join or window function
Converting row-by-row correlated subqueries into set-based joins avoids repeated execution.
EXPLAIN ANALYZE differs from plain EXPLAIN because it:
Answer: Actually runs the query and reports real timings
EXPLAIN ANALYZE executes the query and shows actual versus estimated rows and time.
When estimated rows differ wildly from actual rows in a plan, the fix is usually to:
Answer: Update/refresh table statistics
Large estimate-vs-actual gaps point to stale or missing statistics misleading the planner.
Z-ordering / data clustering on commonly filtered columns helps by:
Answer: Co-locating related values so more files can be skipped
Clustering sorts related values together, improving data-skipping via file-level min/max stats.
A query joining on columns of mismatched data types (e.g., string vs int) often slows down because:
Answer: Implicit casting prevents index use
Type mismatches force implicit conversions that make predicates non-sargable.
Reducing shuffle volume in a distributed aggregation is best achieved by:
Answer: Pre-aggregating (combiner/map-side) before the shuffle
Partial aggregation on each node shrinks data before it is shuffled across the network.
An OR condition across two different columns sometimes prevents index use; a common rewrite is:
Answer: Splitting into a UNION of two indexable queries
Rewriting OR as a UNION lets each branch use its own index.