Posted on 20 Dec, 2021
PostgreSQL devises multiple "query plans" for executing a query in the most optimized & best possible way. You can use EXPLAIN
command to see what query plan a planner creates
=> EXPLAIN SELECT * FROM users;
QUERY PLAN
----------------------------------------------------------
Seq Scan on users (cost=0.00..11.62 rows=362 width=332)
(1 row)
Actually executes the command, returning the execution plan and real statistics.
⚠️ UseEXPLAIN
, if you want to see the execution plan without running it, or use a transaction and do an immediate rollback.
=> EXPLAIN ANALYZE SELECT * FROM users;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..11.62 rows=362 width=332) (actual time=0.018..0.376 rows=365 loops=1)
Planning Time: 0.056 ms
Execution Time: 0.648 ms
(3 rows)
-
Sequential Scan
A sequential scan (or seq scan) reads the rows from the table, in order.
-
Planning Time
Time it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting.
-
Execution Time
The time shown by
EXPLAIN ANALYZE
includes executor start-up and shut-down time, as well as the time to run any triggers that are fired. Doesn't include parsing, rewriting, or planning time. -
Plan Rows
The number of rows, per-loop, that the planner expects to be returned by the operation.
-
Plan Width
The estimated average size of each row returned by the operation, in bytes.
-
Startup Cost
The estimated cost of returning the first row.
-
Total Cost
The estimated cost of returning all rows, by the operation and its descendents.