Re: Regarding EXPLAIN and width calculations

On Fri, Nov 19, 2010 at 12:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
>> What influences the calculation of the 'width' value in query plans?
>
> It's generally the sum of the estimated column widths for all the
> columns needed at that particular level of the plan.
>
>> 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.
>
> Hard to comment about this with such an incomplete view of the situation
> --- in particular, data types would be a critical factor, and I also
> wonder if you're admitting to all the columns involved.

Here is an example that, while super ugly, does show the problem:

begin;
create temporary table foo_1 as
SELECT
CAST(CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) || '.'
|| CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) AS inet) as
a,
(x % 1000) as b
FROM generate_series( 1, 1000000 ) AS x;
create temporary table foo_2 as
SELECT
CAST(CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) || '.'
|| CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) AS inet) as
a,
(x % 1000) as b
FROM generate_series( 1, 1000000 ) AS x;
create temporary table foo_3 as
SELECT
CAST(CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) || '.'
|| CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) AS inet) as
a,
(x % 1000) as b
FROM generate_series( 1, 1000000 ) AS x;
create temporary table foo_4 as
SELECT
CAST(CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) || '.'
|| CAST(x % 255 AS text) || '.' || CAST(x % 255 AS text) AS inet) as
a,
(x % 1000) as b
FROM generate_series( 1, 1000000 ) AS x;
create index foo_1_a_idx on foo_1 (a);
create index foo_2_a_idx on foo_2 (a);
create index foo_3_a_idx on foo_3 (a);
create index foo_4_a_idx on foo_4 (a);
analyze foo_1;
analyze foo_2;
analyze foo_3;
analyze foo_4;
explain analyze verbose
select a, b from foo_1 where a = '1.2.3.4'::inet
UNION
select a, b from foo_2 where a = '1.2.3.4'::inet
UNION
select a, b from foo_3 where a = '1.2.3.4'::inet
UNION
select a, b from foo_4 where a = '1.2.3.4'::inet
;

explain analyze verbose
SELECT SUB.a, SUM(SUB.b) AS b FROM
(
select a, b from foo_1 where a = '1.2.3.4'::inet
UNION ALL
select a, b from foo_2 where a = '1.2.3.4'::inet
UNION ALL
select a, b from foo_3 where a = '1.2.3.4'::inet
UNION ALL
select a, b from foo_4 where a = '1.2.3.4'::inet
) AS SUB GROUP BY a;
rollback;

--
Jon