EXPLAIN ANALYZE for SQL Server Execution Plan Users
On this page
SSMS execution plans vs EXPLAIN
If you've tuned queries in SSMS, you know the job: look at the plan, find the expensive operator, compare estimated and actual rows, fix the index or the statistics. PostgreSQL works the same way. What's new is the vocabulary, the text format and a few habits. This lesson maps one to the other.
| SSMS | PostgreSQL |
|---|---|
| Display Estimated Execution Plan | EXPLAIN |
| Include Actual Execution Plan | EXPLAIN ANALYZE |
| SET STATISTICS IO ON | Buffers, included with ANALYZE since PostgreSQL 18 |
| SET STATISTICS TIME ON | actual time on each node, plus Planning and Execution Time |
| Query hints, plan guides, Query Store forcing | No built-in hints. Fix statistics, indexes or the query. |
| Plan cache, sys.dm_exec_query_stats | No shared plan cache. pg_stat_statements for workload stats (Day 20). |
Two rows deserve a note. PostgreSQL plans most queries fresh each time, and there's no server-wide plan cache like SQL Server's. Prepared statements can reuse a generic plan within one session. And there are no hints in core PostgreSQL. You influence the planner through statistics, indexes, settings and query shape. The pg_hint_plan extension adds hints, but treat it as a last resort.
EXPLAIN versus EXPLAIN ANALYZE
EXPLAIN shows the plan the planner would choose, with estimates, without running the query.
EXPLAIN ANALYZE runs the query and adds what really happened. For a SELECT that's fine. For an UPDATE or DELETE, the changes really happen, so wrap it in a transaction you roll back:
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE order_date < '2024-02-01';
ROLLBACK;Reading an EXPLAIN ANALYZE plan
Here's a join on a test database with 20,000 customers and 300,000 orders:
EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-09-01';Hash Join (cost=578.00..6259.96 rows=7983 width=22) (actual time=7.749..28.881 rows=7800 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
Buffers: shared hit=2039
-> Seq Scan on orders o (cost=0.00..5661.00 rows=7983 width=12) (actual time=0.081..19.069 rows=7800 loops=1)
Filter: (order_date >= '2026-09-01'::date)
Rows Removed by Filter: 292200
-> Hash (cost=328.00..328.00 rows=20000 width=18) (actual ...)
-> Seq Scan on customers c (cost=0.00..328.00 rows=20000 width=18) (actual ...)
Execution Time: 29.604 msHow to read it, compared with SSMS:
- The tree is text. Each
->is a child feeding rows to the line above it. SSMS draws data flowing right to left. Here it flows from the most indented lines up to the top. - Two sets of numbers per node.
cost=578.00..6259.96is the estimated cost to the first row and to the last row, in planner units, not milliseconds.rowsis estimated rows. In the second brackets,actual timeis in milliseconds,rowsis the actual row count, andloopsis how many times the node ran. On PostgreSQL 18 actual rows print with two decimals, as inrows=7800.00, because they're averages per loop. The plans here are trimmed to whole numbers. - Estimated against actual rows is the same check you do in SSMS. 7,983 estimated against 7,800 actual is close, so the statistics are good. A gap of 10 times or more usually means stale statistics, which Day 18 covers.
Rows Removed by Filter: 292200is the giveaway. PostgreSQL read all 300,000 orders to keep 7,800. That's a missing index.- Buffers replace STATISTICS IO.
shared hitmeans pages found in memory, like logical reads.readmeans pages fetched from disk or the OS cache, like physical reads.
Add an index on order_date and run it again:
CREATE INDEX orders_order_date_idx ON orders (order_date);Hash Join (actual time=6.477..10.234 rows=7800 loops=1)
-> Bitmap Heap Scan on orders o (actual time=0.410..1.822 rows=7800 loops=1)
-> Bitmap Index Scan on orders_order_date_idx (actual ...)
-> Hash
-> Seq Scan on customers c
Execution Time: 10.933 msExecution time dropped from 30 ms to 11 ms, and buffers from about 2,000 pages to under 500. Scanning all of customers is still right here, because the hash join needs every customer to match against 7,800 orders.
Plan operators: PostgreSQL vs SSMS
| PostgreSQL node | Closest SSMS operator | Notes |
|---|---|---|
| Seq Scan | Table Scan, Clustered Index Scan | Reads the whole table |
| Index Scan | Index Seek + Key Lookup | Every PostgreSQL index needs a lookup into the table, see Day 16 |
| Index Only Scan | Seek on a covering index | Needs every column in the index and a recently vacuumed table |
| Bitmap Index Scan + Bitmap Heap Scan | No direct equivalent | Collects matches first, then reads table pages in physical order |
| Nested Loop | Nested Loops | Same idea |
| Hash Join | Hash Match (join) | Same idea |
| Merge Join | Merge Join | Same idea |
| HashAggregate / GroupAggregate | Hash Match (aggregate) / Stream Aggregate | Same idea |
| Sort | Sort | Spills to disk if work_mem is too small |
Bitmap scans are the main new one. When an index matches more rows than a seek-and-lookup handles well, but too few for a full scan, PostgreSQL first collects the matching page locations from the index, then reads those pages in physical order. Think of it as a smarter key lookup for medium-sized results.
The visual plan in DataGrip
DataGrip draws plans like SSMS does. Put the cursor in a query, right-click and choose Explain Plan, or the analyze variant, which runs the query just like EXPLAIN ANALYZE. You get a tree with the expensive nodes highlighted, and tooltips with every number.
Use it day to day, but stay fluent in the text format. Plans pasted into tickets, captured in logs by auto_explain or posted in forums are always text.
Check yourself
PostgreSQL read all 5 million rows and threw away 4,990,000 of them to return 10. The query is selective, so an index on the filtered column, or a composite index matching the whole WHERE clause, would let it go straight to those rows. Rerun EXPLAIN ANALYZE afterwards to confirm an index scan.
Find out why the planner chose differently, usually stale statistics or a missing index, and fix that. Core PostgreSQL has no hints. SET enable_nestloop = off in a session can test a theory, but don't ship it as a fix.
Each loop is cheap, but it ran 48,000 times, so its total is roughly 48,000 times the time shown. Check why the planner expected so few outer rows.
It shows how many pages each node read from PostgreSQL's cache (shared hit) versus from disk or the OS cache (read). That tells you whether a slow query is CPU-bound or I/O-bound, which SSMS shows separately as logical and physical reads.
The planner chooses based on table sizes, statistics and settings, which differ between environments. A plan on a small dev copy may use a sequential scan that's correct there and wrong on 50 million rows. Capture plans where the problem happens, ideally with auto_explain.