Optimizing Query Performance Flashcards

A query filtering on a high-cardinality column does a full table scan despite an index existing. What is the MOST likely cause?

Answer: The index is on a different column than the predicate

If the index does not cover the filtered column, the planner cannot use it and falls back to a scan.

Which join strategy is typically fastest when joining a very large table to a very small lookup table?

Answer: Broadcast (map-side) join

Broadcasting the small table to every node avoids shuffling the large table.

What does pushing a WHERE filter down to the storage/scan layer (predicate pushdown) primarily reduce?

Answer: Rows read and transferred upward

Predicate pushdown filters early so fewer rows move through the pipeline.

A columnar format like Parquet improves analytical query speed mainly because it allows:

Answer: Reading only the columns a query needs

Columnar storage lets engines skip unreferenced columns, cutting I/O.

Stale table statistics most directly cause which problem?

Answer: Poor planner cardinality estimates and bad plans

The optimizer relies on statistics to estimate row counts and choose plans.

Which is the best first step when a query is suddenly slow in production?

Answer: Examine the query execution plan

The execution plan reveals scans, join methods, and bottlenecks before any change.

Partition pruning speeds up queries by:

Answer: Skipping partitions that cannot match the filter

When a query filters on the partition key, the engine reads only relevant partitions.