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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
