Thread: possible minor EXPLAIN bug?

Hello

I thing so Agg node doesn't set width well:

postgres=# explain select a,b from twocol;                       QUERY PLAN
----------------------------------------------------------Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
(1 row)

postgres=# explain select sum(a) from twocol group by b;                          QUERY PLAN
----------------------------------------------------------------HashAggregate  (cost=42.10..44.60 rows=200 width=8) --<
wrongshould be 4  ->  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
 
(2 rows)

Agg get width directly from outer plan, what could be wrong.

Regards
Pavel Stehule

On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:

> postgres=# explain select sum(a) from twocol group by b;
>                            QUERY PLAN
> ----------------------------------------------------------------
>  HashAggregate  (cost=42.10..44.60 rows=200 width=8) --< wrong should be 4
>    ->  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
> (2 rows)

Although column b is not displayed it is kept in the HashAgg node to
allow your request to GROUP BY B. I'm happy that it tells me the width
of 8 so I can work out space used by hash, but perhaps it should say 12
(or even 16) to include hash value also, so we include the full cost per
row in the hash table.

If you do  explain select sum(a) from twocol
you will see the width is only 4

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

2008/8/19 Simon Riggs <simon@2ndquadrant.com>:
>
> On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:
>
>> postgres=# explain select sum(a) from twocol group by b;
>>                            QUERY PLAN
>> ----------------------------------------------------------------
>>  HashAggregate  (cost=42.10..44.60 rows=200 width=8) --< wrong should be 4
>>    ->  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
>> (2 rows)
>
> Although column b is not displayed it is kept in the HashAgg node to
> allow your request to GROUP BY B. I'm happy that it tells me the width
> of 8 so I can work out space used by hash, but perhaps it should say 12
> (or even 16) to include hash value also, so we include the full cost per
> row in the hash table.
>
> If you do
>  explain select sum(a) from twocol
> you will see the width is only 4

yes, Agg get this value directly, but it wrong

postgres=# explain select * from (select sum(a) from twocol group by b
offset 0) c;                                QUERY PLAN
----------------------------------------------------------------------------Subquery Scan c  (cost=42.10..46.60
rows=200width=8)  ->  Limit  (cost=42.10..44.60 rows=200 width=8)        ->  HashAggregate  (cost=42.10..44.60 rows=200
width=8)             ->  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
 
(4 rows)

limit, subquery scan has wrong width now.

regards
Pavel
>
> --
>  Simon Riggs           www.2ndQuadrant.com
>  PostgreSQL Training, Services and Support
>
>

"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> I thing so Agg node doesn't set width well:

The planner doesn't really bother to set the width correctly for any
expression-computing node.  This is partly laziness, but OTOH it is
very hard to estimate a sane width for any function returning a
variable-width data type; eg what are the odds of a useful answer forselect repeat(textcol, intcol) from tab1;
For plan nodes that return just Vars it's easier, since we generally
have got stats about average column widths.

I think Agg just copies the width of its input ...
        regards, tom lane