Home > Mobile >  How to read costs in Postgres explain statements?
How to read costs in Postgres explain statements?

Time:03-05

Here's an example explain from postgres:

 Aggregate  (cost=55881.29..55881.30 rows=1 width=64)
   ->  Nested Loop  (cost=1509.25..55881.28 rows=1 width=32)
         ->  Nested Loop  (cost=1508.82..55880.82 rows=1 width=23)
               ->  Nested Loop  (cost=1508.53..55880.48 rows=1 width=19)
                     Join Filter: (n.id = ci.person_id)
                     ->  Nested Loop  (cost=1508.09..55874.34 rows=1 width=27)
                           ->  Nested Loop  (cost=1507.67..55873.73 rows=1 width=23)
                                 ->  Nested Loop  (cost=1507.24..55865.27 rows=1 width=4)
                                       ->  Seq Scan on info_type it  (cost=0.00..2.41 rows=1 width=4)
                                             Filter: ((info)::text = 'mini biography'::text)
                                       ->  Bitmap Heap Scan on person_info pi  (cost=1507.24..55862.85 rows=1 width=8)
                                             Recheck Cond: (info_type_id = it.id)
                                             Filter: ((note)::text = 'Volker Boehm'::text)
                                             ->  Bitmap Index Scan on info_type_id_person_info  (cost=0.00..1507.24 rows=137974 width=0)
                                                   Index Cond: (info_type_id = it.id)
                                 ->  Index Scan using name_pkey on name n  (cost=0.43..8.46 rows=1 width=19)
                                       Index Cond: (id = pi.person_id)
                                       Filter: (((name_pcode_cf)::text >= 'A'::text) AND ((name_pcode_cf)::text <= 'F'::text) AND (((gender)::text = 'm'::text) OR (((gender)::text = 'f'::text) AND ((name)::text ~~ 'B%'::text))))
                           ->  Index Scan using person_id_aka_name on aka_name an  (cost=0.42..0.60 rows=2 width=4)
                                 Index Cond: (person_id = n.id)
                                 Filter: ((name)::text ~~ '%a%'::text)
                     ->  Index Scan using person_id_cast_info on cast_info ci  (cost=0.44..4.40 rows=139 width=8)
                           Index Cond: (person_id = an.person_id)
               ->  Index Only Scan using linked_movie_id_movie_link on movie_link ml  (cost=0.29..0.32 rows=2 width=4)
                     Index Cond: (linked_movie_id = ci.movie_id)
         ->  Index Scan using title_pkey on title t  (cost=0.43..0.46 rows=1 width=21)
               Index Cond: (id = ci.movie_id)
               Filter: ((production_year >= 1980) AND (production_year <= 1995))

I understand the the cost=a...b means a is the startup cost and b is the total cost. But the total cost of what? The total cost of everything that happens in it, or just the action? For example, the cost of a Nested Loop, is it the cost of the nested loop itself or that cost plus everything that happens inside it (more joins, table scans etc)? Thanks.

CodePudding user response:

From the documentation: https://www.postgresql.org/docs/11/using-explain.html

It's important to understand that the cost of an upper-level node includes the cost of all its child nodes. It's also important to realize that the cost only reflects things that the planner cares about. In particular, the cost does not consider the time spent transmitting result rows to the client, which could be an important factor in the real elapsed time; but the planner ignores it because it cannot change it by altering the plan. (Every correct plan will output the same row set, we trust.)

  • Related