How do you read explain analyze PostgreSQL?
What to focus on in EXPLAIN ANALYZE output
- Find the nodes where most of the execution time was spent.
- Find the lowest node where the estimated row count is significantly different from the actual row count.
- Find long running sequential scans with a filter condition that removes many rows.
What is execution time in explain analyze?
Now: Execution time on EXPLAIN output is time server spent on steps 3-4. Planning time on EXPLAIN output is time server spent on step 2 only. I believe you think of it as “time planner thinks that query would take”, but that can be called “planned [execution] time” or “estimated execution time”.
When to use explain or analyze in PostgreSQL?
Used with ANALYZE, EXPLAIN will also show the time spent on executing the query, sorts, and merges that couldn’t be done in-memory, and more.
How to calculate query time in PostgreSQL query planner?
To the query planner, all the data on disk is basically the same. To determine the fastest way to reach a particular piece of data requires some estimation of the amount of time it takes to do a full table scan, a merge of two tables, and other operations to get data back to the user.
What is the actual time of the explain query?
Because we ran EXPLAIN with the ANALYZE option, the query was actually executed and timing information was captured. (actual time=0.049..0.049 rows=100 loops=1) means that the index scan was executed 1 time (the loops value), that it returned 100 rows, and that the actual time was 0..
How much memory is used in a PostgreSQL query?
The memory usage makes sense at 100 rows * 244 bytes = 24.4 kB, which is close enough to the 28kB for our purposes that we can assume it’s the memory taken by the Hash keys themselves. Next, Postgres reads all 10000 rows from tenk2 (aliased as t2) and checks them against the Hash of tenk1 rows.