Re: Plan targetlists in EXPLAIN output

On Thu, 17 Apr 2008 20:42:49 +0200, Simon Riggs <simon@2ndquadrant.com>
wrote:

> On Thu, 2008-04-17 at 12:34 -0400, Tom Lane wrote:
>
>> I'm tempted to propose redefining the currently-nearly-useless
>> EXPLAIN VERBOSE option as doing this.
>
> Yes please.
>
> Sounds like a good home for other useful things also.
>
> I'd like to have an EXPLAIN mode that displayed the plan without *any*
> changeable info (i.e. no costs, row counts etc). This would then allow
> more easy determination of whether plans had changed over time. (But
> EXPLAIN TERSE sounds silly).
>
Plan = TreeTree = XML

EXPLAIN ANALYZE SELECT * FROM test NATURAL JOIN test2 WHERE id
=ANY('{3,666,975,521'});                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
NestedLoop  (cost=17.04..65.13 rows=1 width=8) (actual   
time=51.835..51.835 rows=0 loops=1)   Join Filter: (test.value = test2.value)   ->  Bitmap Heap Scan on test
(cost=17.04..31.96rows=4 width=8)   
(actual time=16.622..16.631 rows=4 loops=1)         Recheck Cond: (id = ANY ('{3,666,975,521}'::integer[]))         ->
BitmapIndex Scan on test_pkey  (cost=0.00..17.04 rows=4   
width=0) (actual time=16.613..16.613 rows=4 loops=1)               Index Cond: (id = ANY
('{3,666,975,521}'::integer[]))  ->  Index Scan using test2_pkey on test2  (cost=0.00..8.28 rows=1   
width=8) (actual time=8.794..8.795 rows=1 loops=4)         Index Cond: (test2.id = test.id)

EXPLAIN XML ...

<NestedLoop>      <Join Filter="(test.value = test2.value)">    <BitmapHeapScan Target="test" RecheckCond="(id) = ANY
($1)"/>    <BitmapIndexScan Index="test_pkey" Cond="id = ANY ('$1'::integer[]))" /></Join><IndexScan Index="test2_pkey"
Target="test2"Cond="test2.id = test.id" /> 
</NestedLoop>

Nicely parsable and displayable in all its glory in pgadmin ;)