Last updated: 2026-07-10
Execution Plans
Visualize SQL execution plans to understand how the database engine processes your queries. Identify performance bottlenecks, compare plans, and optimize slow queries with interactive graphical analysis.
Getting Started
Execution plans show the step-by-step operations the database uses to execute your query. Understanding these plans is essential for query optimization and performance tuning.
How to View an Execution Plan
- Open a query editor with your SQL statement
- Click Query > Display Estimated Plan to see the plan without executing
- Or execute the query and click Execution Plan tab for the actual plan
- The plan displays as an interactive diagram you can explore
Estimated plans show what the optimizer predicts. Actual plans show what really happened, including row counts and execution times. Use actual plans for accurate performance analysis.
Understanding the Plan Diagram
The execution plan displays as a flow diagram where data moves from right to left. Each node represents an operation, and the arrows show how data flows between operations.
Reading Operator Nodes
Each operator node shows key information:
- Operator name - The type of operation (Scan, Seek, Join, etc.)
- Cost percentage - Relative cost compared to the total query
- Row count - Number of rows processed (estimated or actual)
- Subtree cost - Cumulative cost of this operator and its children
Common Operator Types
Table Scan
Reads entire table. Consider adding indexes for large tables.
Index Seek
Efficient lookup using an index. This is what you want to see.
Nested Loops
Joins by looping through rows. Efficient for small datasets.
Hash Match
Builds hash table for joins. Efficient for large datasets.
Sort
Orders result rows. Memory-intensive for large results.
Key Lookup
Fetches additional columns. May indicate need for covering index.
Comparing Execution Plans
Plan comparison helps you understand how query changes or index modifications affect performance.
How to Compare Plans
- Save the first execution plan by right-clicking and selecting Save Plan
- Make your changes (modify query, add index, etc.)
- Generate the new execution plan
- Click Compare Plans and select the saved plan
- Review the side-by-side comparison with highlighted differences
Key Capabilities
- Interactive diagram - Click operators to see detailed properties
- Cost breakdown - See which operations are most expensive
- Plan comparison - Compare before/after to validate optimizations
- Export plans - Save and share plans in standard formats
PostgreSQL: Planning/Execution Time Summary and IO Statistics (PostgreSQL 19, beta)
Every PostgreSQL actual plan now shows a compact summary strip under the statement title in the Statements list — Planning Time and Execution Time, taken directly from the values PostgreSQL itself returns in the EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output. This works on any supported PostgreSQL version (these fields have existed since PostgreSQL 16); it's a plan-viewer fix, not a new PostgreSQL 19 capability.
On PostgreSQL 19+ specifically, actual-plan capture also adds the IO EXPLAIN option, which attaches extra IO counters to scan-type operator nodes: I/O Count, I/O Waits, Average I/O Size, Average Prefetch Distance, Max Prefetch Distance, Prefetch Capacity, and Average I/Os In Progress. Click a scan operator (for example a Seq Scan or Index Scan node) and open the Details panel to see them alongside the operator's other properties. On PostgreSQL 16–18, Jam SQL Studio detects the server version automatically and never requests the IO option, so the plan output is unchanged.
EXPLAIN IO option.Oracle Execution Plans
Jam SQL Studio supports Oracle execution plans using EXPLAIN PLAN and DBMS_XPLAN. The plan is displayed in the same interactive diagram used for SQL Server and PostgreSQL.
How Oracle Plans Work
When you request an execution plan for an Oracle query, Jam SQL Studio runs EXPLAIN PLAN FOR followed by SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) to retrieve the plan output. The textual output is parsed into the interactive tree and graph views.
Oracle-Specific Operators
TABLE ACCESS FULL
Full table scan. Consider adding indexes or partitioning.
INDEX RANGE SCAN
Efficient range lookup using a B-tree index.
HASH JOIN
Hash-based join for large datasets. Common in Oracle.
NESTED LOOPS
Row-by-row join. Efficient for small result sets with indexes.
Oracle execution plans may include hints about statistics staleness or missing indexes. Check the Note section at the bottom of DBMS_XPLAN output for optimizer recommendations.
Performance Optimization Tips
What to Look For
- High-cost operators - Focus optimization on the most expensive operations
- Table scans on large tables - Consider adding appropriate indexes
- Key lookups - May indicate need for covering indexes
- Large row estimates vs actuals - Statistics may be outdated
- Parallelism - Queries using multiple cores for large operations
Common Optimizations
- Add indexes for columns used in WHERE, JOIN, and ORDER BY
- Update statistics if estimated row counts differ from actual
- Rewrite queries to avoid unnecessary operations
- Use covering indexes to eliminate key lookups
- Consider query hints for specific optimization needs
Frequently asked questions
How do I view an execution plan in Jam SQL Studio?
Click the Execute dropdown and select 'Execute with Actual Plan' or 'Execute with Estimated Plan'. Actual plans show real runtime statistics, while estimated plans show the optimizer's predictions without running the query.
What is the difference between estimated and actual execution plans?
Estimated plans show what the query optimizer predicts will happen without running the query. Actual plans show what really happened during execution, including row counts, memory grants, and execution times. Use actual plans to diagnose performance issues.
How do I identify slow operations in an execution plan?
Look for operators with high cost percentages (shown on each node), table scans instead of index seeks, thick arrows indicating large data flows, and warning icons for issues like missing indexes or implicit conversions.
Can I compare two execution plans?
Yes, open two plans and click Compare Plans in the toolbar. This shows a side-by-side diff highlighting differences in operators, costs, and row estimates - useful for comparing before and after query optimizations.
How do I save and share an execution plan?
Click Export in the execution plan toolbar to save as .sqlplan (XML format) for sharing with team members. They can import the file into Jam SQL Studio or other tools that support SQL Server plan files.
Ready to Optimize Your Queries?
Download Jam SQL Studio and start analyzing execution plans today.