What influences the calculation of the 'width' value in query plans?
Specifically, I have two queries which both query the same set of
tables via either UNION or UNION ALL based on the presence (or
absence) of an aggregate function.
Like this:
SELECT a, b FROM foo_1 WHERE a = 'bar'
UNION
SELECT a, b FROM foo_2 WHERE a = 'bar'
UNION
SELECT a, b FROM foo_3 WHERE a = 'bar'
UNION
SELECT a, b FROM foo_4 WHERE a = 'bar'
or this:
SELECT SUB.a, sum(SUB.b) FROM
(SELECT a, b FROM foo_1 WHERE a = 'bar'
UNION ALL
SELECT a, b FROM foo_2 WHERE a = 'bar'
UNION ALL
SELECT a, b FROM foo_3 WHERE a = 'bar'
UNION ALL
SELECT a, b FROM foo_4 WHERE a = 'bar'
) AS SUB GROUP BY SUB.a
The query plan for both queries has an identical inner portion which
consists of an index scan on each table followed by an append
operation. The widths of each subquery are identical. However, in the
case where the aggregate function is used the calculated width of the
"Append" operation is larger:
[UNION ALL/aggregate present] Append (cost=0.00..271845.68
rows=27180665 width=36)
versus:
[UNION/no aggregate present] Append (cost=0.00..271845.68
rows=27180665 width=11)
The UNION ALL variation uses a HashAggregate and "guesses" a row count
of 200 (which is crazy talk) and inherits the Append width of 36.
The UNION variation uses a Sort/Unique with a reasonable row count and
the same width as the Append (11).
What's going on there? Why did the UNION ALL Append operation get a
rather larger (more than 3x) row width when, at that state of the
query execution, the contents should be identical to the UNION
variation? Why did the row count go to 200?
--
Jon
