possible minor EXPLAIN bug?

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.com
PostgreSQL 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=200 width=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

"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 for
select 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