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 ;)