PostgreSQL - EXPLAIN - Show execution plan

EXPLAIN
Display the execution plan which PostgreSQL generates.
EXPLAIN ANALYZE
Display more statistics. Display actual run-time statistics.

In order to analyze INSERT, UPDATE, DELETE without affecting the data – run EXPLAIN ANALYZE in a transaction:

BEGIN;

    EXPLAIN ANALYZE sql_statement;

ROLLBACK;

Get plans of a Stored Procedure

Select  PS.total_logical_reads/NullIf(DateDiff(Minute,PS.cached_time,GetDate()),0) N,

        Coalesce(DB_Name(PS.database_id),DB_Name(PSh.dbid),DB_Name(PSph.dbid))

        DBName,

        Object_Schema_Name(Coalesce(PS.object_id,PSh.objectid,PSph.objectid),Coalesce(PS.database_id,PSh.dbid,PSph.dbid))

        SchemaName,

        Coalesce(Object_Name(PS.object_id,PS.database_id),Object_Name(PSh.objectid,PSh.dbid),Object_Name(PSph.objectid,PSph.dbid))

        ObjectName,

        PS.*,

        PSh.text,

        PSph.query_plan

From    (Select Object_Name(object_id), *

        From    sys.dm_exec_procedure_stats (NoLock)

        Where   database_id=DB_ID('DatabaseName')

                And Object_Schema_Name(object_id)='SchemaName'

                And Object_Name(object_id)='SpName'

        Order By total_logical_reads/NullIf(DateDiff(Minute,cached_time,GetDate()),0) Desc) PS

Outer Apply sys.dm_exec_sql_text(PS.sql_handle) PSh

Outer Apply sys.dm_exec_query_plan(PS.plan_handle) PSph;

Actual very different than estimated - what to do in the first step!

You run a query / stored procedure, etc. and - bad performance... The you also see that the actual very different than estimated data in the execution plan.

There are 1,000,000 things to think about, 1,000,000 blogs posts about reasons, actions and so on about such a case.

BUT:

First of all, in those scenarios - before all the complicated things:

• Check the logic that behind the query, 
• Check that this logic is implemented in the query,
• Check that the tables order and joins is acording to the DB structure and logic.

See that the query is written as you would expect according to the database and/or the application logic.

After that - in most of the cases - 90% of the work will be done!

SentryOne Plan Explorer PRO Goes FREE!

SentryOne Plan Explorer is one of the best tool that help to investigate execution plans on SQL Server.

Now it's free!

https://blogs.sentryone.com/greggonzalez/plan-explorer-pro-goes-free

Output list - Lookup columns

In order to see the columns we get from the lookup operation:

Right click on the lookup icon in the execution plan --> Output list

Costs are estimated, lines thickness is actual

This one is important and confusing:

costs are estimated, lines thickness is actual!

SQL Sentry Plan Explorer

SQL Sentry Plan Explorer (sqlSentry) is a FREE tool that builds upon the graphical plan view in SQL Server Management Studio to make query plan analysis more efficient.

It is a Recommended tool to more comfortable view and for better understand of execution plans:

• You can see the relevant icons on the execution plan by sign a part of the query.
• You can see lists of top operations by costs and more, and move from an operation in the table to the plan.
• You have few views of the execution plan: diagram, XML, etc.
• And more...

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

Performance statistics for cached query plans

SELECT

DB_NAME(st.[dbid]) AS DBName,

OBJECT_NAME(st.objectid, st.[dbid]) as ObjectName,

qs.plan_generation_num,

qs.execution_count,

( SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1,

 ((CASE qs.statement_end_offset 

 WHEN -1 THEN DATALENGTH(st.[text])

 ELSE qs.statement_end_offset END 

 - qs.statement_start_offset)/2) + 1)

) AS statement_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE DB_NAME(st.[dbid]) = 'DataBaseName' 

AND OBJECT_NAME(st.objectid, st.[dbid]) = 'ObjectName'

plan_generation_num - number of plan generation for this Object

execution_count - number of executions of the current plan

if plan_generation_num is more than 1 and execution_count 1:

new plan is generated for each execution --> probably not a good situation.

if plan_generation_num is 1 and execution_count is big:

probably good situation.

Some basic and important Execution Plan rules

• Read from Right To Left and Top to Bottom.
• Read it from right to left, as data flow comes from right to left side.
• If you see nested loop, the upper side is the outer query, the lower side is inner query, inner query is executed once for each outer query.
• Follow Arrows until next branch from below.
• The thickness of the arrow reflects the amount of data being passed.
• execution cost of inner side for nested loop is per execution of outer side.
• If you see hash join, it means that hash table is built for upper (outer) query, then for each rows in lower (inner) query, query processor will look into the hash table to find match value.
• The number that displayed as a precentage is the relative cost to the query for the operator.

• Table Scan - scan all the rows of the table in order to select the results set.
• Index Scan - scan all the rows of the indexed table in order to select the results set. We will scan indexed table when all of the table rows, or the most of them will be returned.
• Index Scan - Seek the table, using indexes, in order to select the results set.
• In the most of the cases, seek is better than scan.

http://msdn.microsoft.com/en-us/library/ms175913.aspx

http://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/

Removes all elements from the plan cache

dbcc freeproccache
go
dbcc dropcleanbuffers
go