.png%3Falt%3Dmedia%26token%3D27026fbf-37b4-4258-9087-a27d01e36b98)
.png%3Falt%3Dmedia%26token%3D27026fbf-37b4-4258-9087-a27d01e36b98)
EXPLAIN queries in PostgreSQL
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)
- 1.Sequential ScanA sequential scan (or seq scan) reads the rows from the table, in order.
- 2.Planning TimeTime it took to generate the query plan from the parsed query and optimize it. It does not include parsing or rewriting.
- 3.Execution TimeThe time shown by
EXPLAIN ANALYZEincludes 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. - 4.Plan RowsThe number of rows, per-loop, that the planner expects to be returned by the operation.
- 5.Plan WidthThe estimated average size of each row returned by the operation, in bytes.
- 6.Startup CostThe estimated cost of returning the first row.
- 7.Total CostThe estimated cost of returning all rows, by the operation and its descendents.
- https://www.postgresql.org/docs/current/sql-explain.html
- https://www.postgresql.org/docs/9.4/using-explain.html
- https://planetscale.com/blog/how-read-mysql-explains