Spark SQL performance tips: patterns, pitfalls and fixes

Introduction

By day 86 in any serious data engineering journey, you've likely written hundreds of Spark SQL queries—and noticed that not all of them run at the same speed. The difference between a query that finishes in seconds and one that takes minutes often comes down to a handful of well-known patterns and anti-patterns. In this article, we'll explore Spark SQL performance tips: patterns, pitfalls and fixes that every senior engineer should have in their toolkit.

We'll focus on four concrete strategies that consistently deliver measurable improvements in production workloads: filtering early, avoiding unnecessary column selection, leveraging partition columns, and reading query execution plans. These aren't theoretical optimizations—they're lessons learned across e-commerce platforms, CRM systems, and real-time trading pipelines running Spark at scale.

Filter Before Joining

The most expensive operation in Spark is the shuffle, and joins are the primary culprit. When you join two large tables without filtering first, Spark moves enormous amounts of data across the cluster. A common pitfall is writing queries that look clean and logical but force Spark to shuffle millions of rows unnecessarily.

Consider a real scenario: you're joining customer events (billions of rows) with a product catalog (thousands of rows) to calculate daily revenue. If you join first and filter later, Spark must shuffle billions of event rows across executors. If you filter the events down to just today's data first, you might reduce the shuffle volume by 99%.

# Inefficient: join first, filter after
result = events.join(products, "product_id") \
    .filter(col("event_date") == "2025-03-15") \
    .select("customer_id", "product_name", "amount")

# Efficient: filter before join
filtered_events = events.filter(col("event_date") == "2025-03-15")
result = filtered_events.join(products, "product_id") \
    .select("customer_id", "product_name", "amount")

The second approach reduces the number of rows that participate in the join. Spark's Catalyst optimizer will sometimes push predicates down for you, but explicit filtering before the join makes your intention clear and helps the optimizer make better decisions about broadcast joins.

When you have multiple joins in sequence, apply the same principle at each stage. Filter aggressively after each join before moving to the next one. This is especially critical in trading and ride-hailing systems where you're often working with append-only event streams where most historical data is irrelevant to the current computation.

Avoid SELECT *

Using SELECT * is a convenience that costs real performance in Spark SQL. When you select all columns, you're telling Spark to read and shuffle every column, including those you don't need. In production tables with 50, 100, or even 200 columns, this overhead accumulates quickly.

The cost is especially high in parquet-backed tables, where columnar storage should give you an advantage—but only if you're selective about which columns you actually need. Reading unnecessary columns means more I/O, more network traffic during shuffles, and more memory pressure on executors.

# Inefficient: reads all columns
result = spark.sql("""
    SELECT * FROM user_transactions
    WHERE transaction_date = '2025-03-15'
""")

# Efficient: select only what you need
result = spark.sql("""
    SELECT user_id, transaction_id, amount, timestamp
    FROM user_transactions
    WHERE transaction_date = '2025-03-15'
""")

This becomes even more important when you're chaining operations. Each transformation that inherits all columns from the previous one carries forward that overhead. In a multi-stage pipeline, avoiding SELECT * at each step compounds the benefit.

The rule is simple: always name the columns you need. Even if that means typing out a dozen column names, the performance gain is worth it. Your code is also more maintainable—future readers understand immediately which columns matter for your calculation.

Use Partition Columns in Filters

Partition pruning is one of the most powerful optimization levers available in Spark. When your table is partitioned by date, region, or tenant ID, filtering on those columns allows Spark to skip entire directories of data without even reading them. A query that scans a year of data can suddenly touch only a week's worth of files.

We've seen this pattern repeatedly in our e-commerce and SaaS workloads: tables partitioned by date, queried with date range filters. The difference between explicitly filtering on the partition column and hoping Catalyst will figure it out is often 10–50x in execution time.

# Without partition filter: Spark may scan all partitions
result = spark.sql("""
    SELECT order_id, customer_id, total
    FROM orders
    WHERE customer_id = 'CUST123' AND total > 100
""")

# With partition filter: Spark skips partitions by year, month, day
result = spark.sql("""
    SELECT order_id, customer_id, total
    FROM orders
    WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01'
    AND customer_id = 'CUST123' AND total > 100
""")

The second query filters on order_date, which is the partition column. Spark's file listing is now constrained to the relevant date range, avoiding unnecessary I/O. This is especially critical in data lakes with terabytes of historical data.

When designing your table schemas, choose partition columns that align with your query patterns. If 90% of your queries filter by date, partition by date. If you regularly filter by customer or region, consider multi-level partitioning. Just remember that over-partitioning (too many partition columns or too many small partitions) creates its own problems—aim for partitions that contain 100 MB to a few gigabytes of data each.

Check Plans with EXPLAIN

The easiest way to understand why a query is slow is to look at its execution plan. Spark provides the EXPLAIN command, which shows you exactly what Spark intends to do. Learning to read these plans is a critical skill.

There are two levels of EXPLAIN: the logical plan (what you want to compute) and the physical plan (how Spark will compute it). The physical plan is usually more informative—it shows join strategies, shuffle operations, and how many partitions are involved.

# Check the physical execution plan
spark.sql("""
    SELECT customer_id, SUM(amount) as total_spent
    FROM transactions
    WHERE transaction_date = '2025-03-15'
    GROUP BY customer_id
""").explain(mode="extended")

# Output will show:
# - Scan with partition filters
# - HashAggregate or SortAggregate
# - Number of partitions
# - Shuffle operations

Key things to look for in the plan:

• Shuffle operations: These are expensive. If you see unnecessary shuffles, look for ways to filter or repartition before that point.
• Broadcast joins: For small tables joined with large ones, you want to see "BroadcastHashJoin" rather than "SortMergeJoin".
• Partition counts: If you see "number of partitions = 200" but your data is small, you're likely over-partitioned and should coalesce.
• Filter pushdown: Check that your filters are applied at the scan level, not after reading all data.

In a recent trading system we worked on, a query was taking 45 seconds. The EXPLAIN output revealed that Spark was doing a SortMergeJoin when a BroadcastJoin would have been much faster. Adding a hint to force the broadcast reduced execution time to 2 seconds. The query logic was fine—the execution strategy needed adjustment.

Common Mistakes

Several patterns appear repeatedly in slow queries:

• Joining without filtering first: We covered this, but it bears repeating because it's so common. Always reduce row counts before joins.
• Selecting all columns "just in case": The cost of reading unused columns far exceeds the convenience of not typing their names.
• Ignoring partition columns: A table partitioned by date is worthless if your filters don't mention the date. This is a design problem masquerading as a query problem.
• Never reading EXPLAIN output: Some teams write queries, notice they're slow, and add hints or caching without understanding why they're slow. EXPLAIN should be your first debugging step.
• Assuming Catalyst is perfect: Catalyst is powerful but not magical. It makes reasonable decisions with the information available. Explicit predicates and column selection help it make better choices.

Key Takeaways

• Filter early and aggressively, especially before joins. Reducing row counts before expensive operations is the highest-impact optimization pattern.
• Always select only the columns you need. SELECT * reads unnecessary data and slows down every downstream operation.
• Include partition column filters in every query. This enables partition pruning and can reduce execution time by orders of magnitude.
• Use EXPLAIN to understand your query's execution strategy. Look for unexpected shuffles, join types, and partition counts.
• Remember that performance optimization is iterative. A 10% improvement here, a 20% improvement there, and you've built a fast system.