Re: Regarding EXPLAIN and width calculations - Mailing list pgsql-general

On Fri, Nov 19, 2010 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jon Nelson <jnelson+pgsql@jamponi.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