Waiting for 8.4 – “EXPLAIN VERBOSE”

Tom Lane committed his patch which modifies output of “EXPLAIN VERBOSE" command.

Log message:

Cause EXPLAIN's VERBOSE option to print the target list (output column list)
of each plan node, instead of its former behavior of dumping the internal
representation of the plan tree.  The latter display is still available for
those who really want it (see debug_print_plan), but uses for it are certainly
few and and far between.  Per discussion.
This patch also removes the explain_pretty_print GUC, which is obsoleted
by the change.

What does it do? It's pretty simple. Before applying this patch, “EXPLAIN VERBOSE" output looked like this:

    {AGG
    :startup_cost 74198.53
    :total_cost 79460.00
    :plan_rows 40000
    :plan_width 12
    :targetlist (
       {TARGETENTRY
       :expr
          {VAR
          :varno 0
          :varattno 3
          :vartype 23
          :vartypmod -1
          :varlevelsup 0
          :varnoold 4
          :varoattno 2
          }
       :resno 1
       :resname some_field
       :ressortgroupref 1
       :resorigtbl 780363
       :resorigcol 2
       :resjunk false
       }
       {TARGETENTRY
...

This is pretty … useless. I mean – it's perfectly usable for hard core PostgreSQL hackers, but for the average SQL guy, this output is meaningless.

With Tom's patch however, output of explain verbose looks like this:

 GroupAggregate  (cost=441.91..484.62 rows=1898 width=12)
   Output: c.parent_id, r.parent_id, count(DISTINCT e.id)
   ->  Sort  (cost=441.91..446.66 rows=1898 width=12)
         Output: c.parent_id, r.parent_id, e.id
         Sort Key: c.parent_id, r.parent_id
         ->  Hash Join  (cost=258.32..338.56 rows=1898 width=12)
               Output: c.parent_id, r.parent_id, e.id
               Hash Cond: (bac.category_id = c.child_id)
               ->  Hash Join  (cost=247.92..303.41 rows=1154 width=12)
                     Output: e.id, r.parent_id, bac.category_id
                     Hash Cond: (bac.event_id = e.id)
                     ->  Seq Scan on some_table_name bac  (cost=0.00..29.40 rows=1940 width=8)
                           Output: bac.id, bac.event_id, bac.category_id
                     ->  Hash  (cost=246.43..246.43 rows=119 width=8)
                           Output: e.id, r.parent_id
                           ->  Nested Loop  (cost=0.00..246.43 rows=119 width=8)
                                 Output: e.id, r.parent_id
                                 ->  Seq Scan on other_table e  (cost=0.00..11.20 rows=30 width=8)
                                       Output: e.id, e.user_id, e.entered_on, e.is_active, e.is_public, ...
                                       Filter: (is_active AND (NOT is_expired))
                                 ->  Index Scan using some_index on tablexxx r  (cost=0.00..7.79 rows=4 width=8)
                                       Output: r.id, r.parent_id, r.parent_level, r.child_id, r.child_level, r.depth
                                       Index Cond: (r.child_id = e.region_id)
               ->  Hash  (cost=6.29..6.29 rows=329 width=8)
                     Output: c.parent_id, c.child_id
                     ->  Seq Scan on another_table c  (cost=0.00..6.29 rows=329 width=8)
                           Output: c.parent_id, c.child_id
(27 rows)

This is actually pretty cool. It shows which columns are returned from each level of query execution.

For standard queries it might not look like a big win, but in cases of complicated views with sub selects in ‘SELECT' part – it might help solve some performance issues.

2 thoughts on “Waiting for 8.4 – “EXPLAIN VERBOSE””

  1. Hello. I have always really enjoyed your site, but I must admit, the redesign with new fonts and colors has completely thrown me off. The old design with black, gray & white was simple and beautiful. Sorry, didn’t know where else to post a comment like this.

Comments are closed.