Re: Plan targetlists in EXPLAIN output

On Thu, 17 Apr 2008 20:42:49 +0200, Simon Riggs <simon(at)2ndquadrant(dot)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 = Tree
Tree = XML

EXPLAIN ANALYZE SELECT * FROM test NATURAL JOIN test2 WHERE id
=ANY('{3,666,975,521'});
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop (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.96 rows=4 width=8)
(actual time=16.622..16.631 rows=4 loops=1)
Recheck Cond: (id = ANY ('{3,666,975,521}'::integer[]))
-> Bitmap Index 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 ;)