.png%3Falt%3Dmedia%26token%3D27026fbf-37b4-4258-9087-a27d01e36b98)
.png%3Falt%3Dmedia%26token%3D27026fbf-37b4-4258-9087-a27d01e36b98)
Today I Learned
Search…
Career
Coding Practices
Databases
Distributed Computing
Git
Go
Miscellaneous
Python
Shell
Vim
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 createsEXPLAIN
EXPLAIN1
=> EXPLAIN SELECT * FROM users;
2
QUERY PLAN
3
----------------------------------------------------------
4
Seq Scan on users (cost=0.00..11.62 rows=362 width=332)
5
(1 row)
Copied!
EXPLAIN ANALYZE
EXPLAIN ANALYZEActually 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.
1
=> EXPLAIN ANALYZE SELECT * FROM users;
2
QUERY PLAN
3
------------------------------------------------------------------------------------------------------
4
Seq Scan on users (cost=0.00..11.62 rows=362 width=332) (actual time=0.018..0.376 rows=365 loops=1)
5
Planning Time: 0.056 ms
6
Execution Time: 0.648 ms
7
(3 rows)
Copied!
Glossary
- 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.
Resources
- https://www.postgresql.org/docs/current/sql-explain.html
- https://www.postgresql.org/docs/9.4/using-explain.html
Copy link
Edit on GitHub