How do you compare SQL query execution plans?

1 Generate execution plans

Before you can compare execution plans, you need to generate them for the queries you want to analyze. There are different ways to do this, depending on your database system and tool. For example, you can use the EXPLAIN or EXPLAIN ANALYZE commands in PostgreSQL, the SET SHOWPLAN_ALL or SET STATISTICS XML options in SQL Server, or the Explain Plan or Autotrace features in Oracle SQL Developer. You can also use third-party tools such as SQL Sentry Plan Explorer, ApexSQL Plan, or dbForge Query Builder to generate and visualize execution plans.

2 Compare execution plans visually

One way to compare execution plans is to look at them visually and compare the shapes, sizes, and colors of the nodes and arrows. This can help you spot differences in the query plans, such as the order of operations, the join types, the indexes used, the parallelism, and the estimated or actual costs. You can use the built-in features of your database tool or a third-party tool to view the execution plans side by side or in tabs. Some tools also provide graphical indicators or annotations to highlight the differences or potential problems in the execution plans.

3 Compare execution plans numerically

Another way to compare execution plans is to look at the numerical values and statistics associated with each node and operator. This can help you quantify the differences in the query plans, such as the number of rows, the CPU time, the I/O time, the memory usage, and the subtree costs. You can use the textual or XML output of your database tool or a third-party tool to view the numerical details of the execution plans. Some tools also provide summary tables or charts to compare the overall performance metrics of the queries.

4 Compare execution plans with baselines

A third way to compare execution plans is to use baselines, which are reference execution plans that represent the optimal or expected performance of a query. You can compare the current execution plans with the baselines to detect any deviations or regressions in the query performance. You can create baselines manually by saving the execution plans of the queries when they are running well, or automatically by using features such as SQL Server Query Store, Oracle SQL Plan Management, or PostgreSQL pg_stat_statements. You can also use third-party tools that support baselines to compare and manage them.

5 Compare execution plans with alternatives

A fourth way to compare execution plans is to use alternatives, which are different versions of the same query that use different syntax, hints, or options. You can compare the execution plans of the alternatives to see how they affect the query performance and choose the best one for your scenario. You can create alternatives manually by modifying the query code, or automatically by using features such as SQL Server Database Engine Tuning Advisor, Oracle SQL Tuning Advisor, or PostgreSQL pgTune. You can also use third-party tools that suggest or generate alternatives to compare and test them.

6 Compare execution plans with best practices

A fifth way to compare execution plans is to use best practices, which are general guidelines or recommendations for writing and optimizing SQL queries. You can compare the execution plans with the best practices to identify and fix any issues or bad habits in the query code. Some of the common best practices are to avoid unnecessary columns, joins, or subqueries, to use appropriate indexes, data types, and functions, to avoid implicit conversions or type mismatches, and to use parameters or bind variables. You can use the feedback or warnings of your database tool or a third-party tool to check the execution plans against the best practices.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?