And Analyze
Explain generates a query plan by estimating the cost.
This is a sequential scan on a table users.
EXPLAIN SELECT * FROM users;
QUERY PLAN
---------------------------------------------------------
Seq Scan on users (cost=0.00..155.00 rows=10000 width=4)
(1 row)We can format this in JSON if we so desire:
EXPLAIN (FORMAT JSON) SELECT * FROM users;
QUERY PLAN
----------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan", +
"Relation Name": "users", +
"Alias": "users", +
"Startup Cost": 0.00, +
"Total Cost": 155.00, +
"Plan Rows": 10000, +
"Plan Width": 4 +
} +
} +
]If there is an index, and the query uses it (normally with a
WHERE statement)
EXPLAIN SELECT * FROM foo WHERE i = 4;
QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)
(2 rows)Explain analyze actually runs the query in question: if you want to
run a query that has side effects (i.e. queries that aren't
SELECTs), you will want to run EXPLAIN ANALYZE
in a transaction.
Always do something like this:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;