Understand Postgres Query Plan

Question

explain analyse
SELECT COUNT(*) FROM syo_event WHERE id_group = 'OPPORTUNITY' AND id_type = 'NEW'

My query have this plan:

 Aggregate  (cost=654.16..654.17 rows=1 width=0) (actual time=3.783..3.783 rows=1 loops=1)
   ->  Bitmap Heap Scan on syo_event  (cost=428.76..654.01 rows=58 width=0) (actual time=2.774..3.686 rows=1703 loops=1)
         Recheck Cond: ((id_group = 'OPPORTUNITY'::text) AND (id_type = 'NEW'::text))
         ->  BitmapAnd  (cost=428.76..428.76 rows=58 width=0) (actual time=2.635..2.635 rows=0 loops=1)
               ->  Bitmap Index Scan on syo_list_group  (cost=0.00..35.03 rows=1429 width=0) (actual time=0.261..0.261 rows=2187 loops=1)
                     Index Cond: (id_group = 'OPPORTUNITY'::text)
               ->  Bitmap Index Scan on syo_list_type  (cost=0.00..393.45 rows=17752 width=0) (actual time=2.177..2.177 rows=17555 loops=1)
                     Index Cond: (id_type = 'NEW'::text)
 Total runtime: 3.827 ms

In the first line:

(actual time=3.783..3.783 rows=1 loops=1

(Why the actual time not match with the last line, Total runtime ?)

In the second line:

cost=428.76..654.01

(Start the Bitmap Heap Scan with cost 428.76 and ends with 654.01) ?

rows=58 width=0)

(Wath is rows and width, anything important ?)

rows=1703

(this is the result)

loops=1)

(Used in subqueries ?)

mr nick · Accepted Answer · 2016-09-15 17:50:42Z

3

From the postgres docs:

Note that the "actual time" values are in milliseconds of real time, whereas the cost estimates are expressed in arbitrary units; so they are unlikely to match up. The thing that's usually most important to look for is whether the estimated row counts are reasonably close to reality.

The estimated cost is computed as (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost). By default, seq_page_cost is 1.0 and cpu_tuple_cost is 0.01

answered Sep 15, 2016 at 17:50

mr nick

5602 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kamil Gosciminski · Accepted Answer · 2016-09-15 17:58:02Z

As for the first line, EXPLAIN executed the query and it took 3.783 ms, but presenting you with the output of the plan takes some time to, so that total runtime is increased by the time spent on doing that.

Basically EXPLAIN ANALYZE displays estimates that a plain EXPLAIN would show you along with values collected from actually running the query, hence the difference in the second line.

Both rows and width are important. Respectively this is the number of rows output estimation and an average width of rows in bytes. Your estimated total cost will be lower if the estimation of rows returned is smaller and you need to take that into account.

To understand what loops actually presents you need to know that a query plan is actually a tree of plan nodes. There are different types of nodes that serve different purposes - a scan node for example is responsible for returning raw rows from a table. If your query is doing some operation on rows there will be additional nodes above the scan nodes to handle that.

The first line in your output from EXPLAIN is a summary from the level 1 (at the top) node with estimated cost for entire plan.

Knowing that, loops represents a value of total number of executions of a particular node. This is because in some plans a subplan node can be executed more than once and if that happens then to make the numbers comparable with other estimates it multiplies time and rows values by loops to get the total time spent in that node.

You can get more insight on the topic in the documentation.

Collectives™ on Stack Overflow

Understand Postgres Query Plan

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related