Reading and comparing query plans across SQL Server, MySQL, and PostgreSQL helps you find performance bottlenecks, verify index usage, and understand how each database optimizes the same query. This guide walks you through generating, reading, and comparing execution plans in all three systems, assuming you have basic SQL knowledge and access to a database instance.
What You’ll Need
- Access to a SQL Server, MySQL, and PostgreSQL instance (local or cloud) with a sample database.
- A query you want to analyze (preferably with realistic data volume).
- Basic familiarity with your database’s command-line or GUI tool (SSMS, MySQL Workbench, pgAdmin).
- Permissions to view execution plans (usually granted by default, but may require SHOW PLAN or EXPLAIN privileges).
Step-by-Step
- Generate the query plan in SQL Server: In SQL Server Management Studio (SSMS), open a new query window, write your query, and press Ctrl+M to enable “Include Actual Execution Plan.” Then execute the query. Alternatively, use
SET SHOWPLAN_ALL ONbefore running the query to get a text plan. The graphical plan appears in a new tab; the text plan appears in the results grid. - Generate the query plan in MySQL: Use the
EXPLAINkeyword before your query. For example:EXPLAIN SELECT * FROM orders WHERE customer_id = 123;. This returns a table with one row per table access step. For a more detailed JSON plan, useEXPLAIN FORMAT=JSON. In MySQL Workbench, you can also click the “Explain” icon (lightning bolt with a magnifying glass) to see a visual plan. - Generate the query plan in PostgreSQL: Use
EXPLAINbefore your query. For example:EXPLAIN SELECT * FROM orders WHERE customer_id = 123;. To see actual execution times and row counts, useEXPLAIN ANALYZE. For a machine-readable format, addFORMAT JSON(e.g.,EXPLAIN (FORMAT JSON, ANALYZE) SELECT ...). In pgAdmin, select the query and press F7 to display the graphical plan. - Read the SQL Server plan: Start from the rightmost icon and move left. Each icon represents an operator (e.g., Clustered Index Scan, Hash Match, Nested Loops). The arrow thickness indicates rows passed. Hover over an operator to see cost percentages, estimated rows, and actual rows. The “Estimated Subtree Cost” is a relative number; lower is better. Look for warnings (yellow triangle) like missing index or implicit conversion.
- Read the MySQL plan: In the EXPLAIN table, read the “id” column (higher = executed first), “select_type”, “table”, “type” (access type: ALL = full scan, index = full index scan, range, ref, eq_ref, const are better), “possible_keys”, “key” (actual index used), “rows” (estimated rows examined), and “Extra” (e.g., “Using index” is good, “Using temporary” or “Using filesort” can be slow). In JSON format, look at “cost_info” and “rows_examined_per_scan”.
- Read the PostgreSQL plan: The plan is a tree of nodes. Each node shows the operation (Seq Scan, Index Scan, Nested Loop, etc.), the table name, and the cost (e.g.,
cost=0.00..22.00). The first number is startup cost, the second is total cost. With ANALYZE, you also get actual time (ms), rows, and loops. Read from the innermost (bottom) nodes outward. The top node’s total cost is the query’s total. Look for “Seq Scan” on large tables (bad), “Index Scan” (good), and “Sort” or “Hash” operations that might be avoidable. - Compare the plans side by side: Run the same query with EXPLAIN on each database. Write down the key metrics: access method (scan vs. seek), estimated rows, actual rows (if available), and any sorting or temp table usage. For a fair comparison, ensure the table structures and indexes are similar (or note differences). Also consider the database version and configuration, as they affect the optimizer.
- Identify common bottlenecks: Look for full table scans on large tables, missing indexes (SQL Server shows a green “Missing Index” hint; MySQL shows “Using index” only if covering; PostgreSQL shows Seq Scan), and expensive sorts or hashes. Compare the estimated vs. actual rows; a big discrepancy suggests stale statistics.
- Use plan analysis tools: For SQL Server, paste the plan XML into
sqlplanor use the built-in “Display Estimated Execution Plan” (Ctrl+L) for a quick read. For MySQL, useEXPLAIN ANALYZEin MySQL 8.0+ to get actual execution times. For PostgreSQL, useEXPLAIN (ANALYZE, BUFFERS)to see buffer hits. - Iterate and optimize: After identifying a bottleneck, create an index, rewrite the query, or update statistics. Re-generate the plan and compare the new metrics to the old ones. Repeat until the plan shows efficient access methods (index seeks, narrow scans) and the cost drops significantly.
Common Mistakes
- Comparing plans without running ANALYZE/UPDATE STATISTICS first; stale statistics lead to misleading plans.
- Ignoring actual vs. estimated rows; a plan can look good on paper but perform poorly at runtime.
- Focusing only on the first operator; the most expensive operator is often in the middle of the plan.
- Assuming a missing index is the only fix; sometimes query rewriting (e.g., avoiding functions in WHERE) is more effective.
- Using GUI tools without understanding the underlying text plan; graphical plans can hide details.
Tips
- Always run EXPLAIN ANALYZE in PostgreSQL to get real execution times; in MySQL, use EXPLAIN ANALYZE (8.0+) or run the query with profiling.
- In SQL Server, use “Include Live Query Statistics” (Ctrl+Alt+L) to see row flow in real time.
- Normalize the query text (remove comments, consistent formatting) before comparing across databases.
- Test with realistic data volumes; plans on tiny tables are often identical and misleading.
- Use the plan’s “cost” as a relative measure, not an absolute one; costs are not comparable across databases.
Troubleshooting
If you cannot generate a plan, check your permissions: you may need SHOW PLAN in MySQL or EXPLAIN privileges in PostgreSQL. If the plan shows a full table scan on a small table, that’s normal; the optimizer may prefer it over an index. If you see “Cannot compute” or an error in MySQL, ensure you’re using EXPLAIN before the query, not inside a transaction. In SQL Server, if the graphical plan doesn’t appear, press Ctrl+M before executing, or use SET STATISTICS PROFILE ON for a text output. If PostgreSQL’s EXPLAIN ANALYZE actually executes the query (which it does), be careful with INSERT/UPDATE/DELETE – use EXPLAIN (without ANALYZE) for those to avoid changes.
Frequently Asked Questions
Q: Can I compare the cost numbers directly between SQL Server, MySQL, and PostgreSQL?
A: No. Each database uses a different costing model. Compare relative improvements within the same database, not raw numbers across databases.
Q: Why does PostgreSQL show a “Seq Scan” even when an index exists?
A: The optimizer may estimate that reading the whole table is cheaper than using the index, especially for small tables or when the query returns a large percentage of rows.
Q: What does “Using filesort” mean in MySQL?
A: It means MySQL has to sort the result set using extra memory or disk, which can be slow. Adding an index on the ORDER BY column often eliminates it.
Q: How do I see the actual execution plan in SQL Server?
A: Press Ctrl+M to enable “Include Actual Execution Plan” before running the query, or use SET STATISTICS XML ON to get the plan in XML format.
Q: Is EXPLAIN ANALYZE safe to run on INSERT/UPDATE/DELETE?
A: No, it actually executes the statement. Use plain EXPLAIN for write operations, or wrap in a transaction and roll back (but be aware of locks).
Q: What is the most important thing to look for in a query plan?
A: Look for full table scans on large tables, missing index warnings, and a large difference between estimated and actual rows. Those are the most common performance killers.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Fix Error 0x8007025d in Windows 10
- Fix Alexa App Stuck on Setup
- Android Camera for 7 Office Tasks: Step-by-Step
- Pair Phone to Pioneer Stereo After Bluetooth Reset
- Fix Windows Not Saving Remote Desktop Credentials
- Essential Tips for macOS Users Switching to Windows