# Debunking the "Filter Early, JOIN Later" SQL Performance Myth > That viral SQL performance tip about filtering before joining? It is complete nonsense. Here is why query optimizers make it irrelevant. Published: 2025-06-07. Author: Milan Jovanović. Canonical: https://milanjovanovic.tech/blog/debunking-the-filter-early-join-later-sql-performance-myth No, filtering in a subquery before a JOIN does not make your SQL faster on a modern database. I tested both versions on PostgreSQL with 10,000 users and 5 million orders, and they produced identical execution plans, each running in about 685ms. The cost-based optimizer rewrites both queries into the same plan, regardless of how you write them. I came across a Medium article with 700+ claps promoting this "SQL performance trick": **"Filter Early, JOIN Later"**
![SQL performance tip that doesn](https://milanjovanovic.tech/blogs/mnw_145/sql_performance_tip.png)
Source: SQL Tricks that Cut My Query Time by 80%
The claim goes like this: instead of joining tables first and then filtering, you should filter in a subquery first, then join. The supposed benefit? > The database filtered the smaller table first, then did the JOIN — saving time and memory. Here is the thing - this advice is **completely wrong** for modern databases. Let me show you why with actual data. ## The Supposed "Optimization" The article shows two queries. Here is the "bad" version: ```sql SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 500; ``` And the "optimized" version: ```sql SELECT * FROM ( SELECT * FROM orders WHERE total > 500 ) o JOIN users u ON u.id = o.user_id; ``` The claim is that the second query is faster because it "filters first, then joins." Sounds logical, right? **Wrong.** ## Testing with Real Data I tested both queries on a PostgreSQL database with: - 10,000 users - 5,000,000 orders (500 per user) - Filtering for orders > $500 Let me run **EXPLAIN ANALYZE** on both queries to see what actually happens. ## The Results Here are the execution plans for both queries: **"Bad" Query Execution Plan:** ``` Hash Join (cost=280.00..96321.92 rows=2480444 width=27) (actual time=1.014..641.202 rows=2499245 loops=1) Hash Cond: (o.user_id = u.id) -> Seq Scan on orders o (cost=0.00..89528.00 rows=2480444 width=14) (actual time=0.006..368.857 rows=2499245 loops=1) Filter: (total > '500'::numeric) Rows Removed by Filter: 2500755 -> Hash (cost=155.00..155.00 rows=10000 width=13) (actual time=0.998..0.999 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 577kB -> Seq Scan on users u (cost=0.00..155.00 rows=10000 width=13) (actual time=0.002..0.341 rows=10000 loops=1) Planning Time: 0.121 ms Execution Time: 685.818 ms ``` **"Optimized" Query Execution Plan:** ``` Hash Join (cost=280.00..96321.92 rows=2480444 width=27) (actual time=1.019..640.613 rows=2499245 loops=1) Hash Cond: (orders.user_id = u.id) -> Seq Scan on orders (cost=0.00..89528.00 rows=2480444 width=14) (actual time=0.005..368.260 rows=2499245 loops=1) Filter: (total > '500'::numeric) Rows Removed by Filter: 2500755 -> Hash (cost=155.00..155.00 rows=10000 width=13) (actual time=1.004..1.005 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 577kB -> Seq Scan on users u (cost=0.00..155.00 rows=10000 width=13) (actual time=0.003..0.348 rows=10000 loops=1) Planning Time: 0.118 ms Execution Time: 685.275 ms ``` **The execution plans are identical.** Both queries took ~685ms. The "optimization" did absolutely nothing. Here's the simplified execution plan, where I removed some details: ``` Hash Join Hash Cond: (o.user_id = u.id) -> Seq Scan on orders o Filter: (total > '500'::numeric) Rows Removed by Filter -> Hash -> Seq Scan on users u ``` The core operations are: 1. Sequential Scan on `orders` table with filter applied 2. Sequential Scan on `users` table 3. Hash operation to build hash table from `users` 4. Hash Join using the hash condition on `user_id` ## Query Optimizers Are Smarter Than You Modern databases use **cost-based optimizers**. Here is what happens when you run a query: 1. **Parser** turns your SQL into an abstract syntax tree 2. **Optimizer** rewrites your query into the most efficient form 3. **Executor** runs the optimized plan The optimizer looks at your query and says: "I don't care how you wrote this. I will figure out the best way to execute it." Both of our queries get rewritten to the same optimal plan: - Filter the orders table first (because that eliminates rows early) - Build a hash table from users (the smaller table) - Hash join the filtered orders with users **The optimizer already does the "optimization" automatically.** Your manual subquery does not make it faster - it just makes your SQL harder to read. ## How Cost-Based Optimization Works The query optimizer has statistics about your tables: - Row counts - Data distribution - Index availability - Column selectivity It uses these stats to estimate the cost of different execution strategies: - Which table to scan first - Which join algorithm to use (hash, nested loop, merge) - When to apply filters - Which indexes to use Then it picks the cheapest plan. Your well-intentioned manual "optimization" gets ignored because the optimizer knows better. ## Summary The "Filter Early, JOIN Later" advice is a relic from ancient database systems that did not have sophisticated optimizers. Modern databases like PostgreSQL, MySQL, and SQL Server already do predicate pushdown and join reordering automatically. Your manual "optimizations" are pointless and make code harder to maintain. Write clear, readable SQL. Let the optimizer do its job. **The real lesson?** Stop believing every performance tip you read online. Use `EXPLAIN ANALYZE` to understand what your database is actually doing. --- ## Frequently asked questions ### Does filtering in a subquery before a JOIN make SQL faster? No, not on modern databases. In a PostgreSQL test with 10,000 users and 5 million orders, the plain JOIN and the pre-filtered subquery version produced identical execution plans and both ran in about 685ms. The optimizer rewrites both queries into the same optimal plan. ### What is a cost-based query optimizer? A cost-based optimizer uses table statistics (row counts, data distribution, index availability, column selectivity) to estimate the cost of different execution strategies. It decides which table to scan first, which join algorithm to use, when to apply filters, and picks the cheapest plan, regardless of how you wrote the SQL. ### What is predicate pushdown in SQL? Predicate pushdown means the database applies WHERE filters as early as possible in the execution plan, eliminating rows before the join happens. Modern databases like PostgreSQL, MySQL, and SQL Server do this automatically, so manually filtering in a subquery before a JOIN adds nothing. ### How do I see what my SQL query is actually doing? Run EXPLAIN ANALYZE on the query. It shows the real execution plan: which scans run, which join algorithm is used, how many rows each step processes, and how long each step takes. It is the reliable way to test a performance tip instead of trusting advice you read online. ### Should I rewrite JOINs as subqueries for performance? No. The optimizer ignores that kind of manual restructuring and produces the same plan, so the subquery only makes your SQL harder to read and maintain. Write clear, readable SQL and let the optimizer do its job. The advice made sense only for ancient databases without sophisticated optimizers.