Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create schema def;
create table t1(a smallint, b int, c double precision, d numeric, e text, f bigint);

insert into t1 
select 
  floor(random()*100)::int, 
  floor(random()*100)::int,
  random()*100,
  random()*100, 
  repeat(gen_random_uuid()::text,floor(random()*5+1)::int),
  floor(random()*9223372036854775807)::bigint
from generate_series(1,300);

select * from t1 limit 10;

CREATE SCHEMA



CREATE TABLE



INSERT 0 300




a
b
c
d
e
f


23
71
1.1721947768125185
16.7528977262813
faf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0ad
2197393462332635136

72
29
39.01667117618446
93.9129676126963
a26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234e
5833481516626774016

81
80
58.70090848363101
42.8630097743508
36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a
3033801512388929536

97
33
86.72729390270628
7.83294175433189
104dfce4-96c7-40f4-ad91-e8098e09cfa3104dfce4-96c7-40f4-ad91-e8098e09cfa3104dfce4-96c7-40f4-ad91-e8098e09cfa3
4900319619640123392

24
76
50.437690834132695
85.1301868094659
877a91dd-7e79-4967-bc58-f90c5804d010877a91dd-7e79-4967-bc58-f90c5804d010
7976803432216248320

93
64
11.57090610979723
16.6931001402385
ccf7f439-02b7-419b-ba50-54356e6bafffccf7f439-02b7-419b-ba50-54356e6bafffccf7f439-02b7-419b-ba50-54356e6bafff
5839714898056380416

76
37
24.573926476861008
97.3345531773341
88b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd0
495360858684590080

99
51
32.70351635877666
7.55685924071043
f3d3b592-43e9-412b-b313-15f81c23d951f3d3b592-43e9-412b-b313-15f81c23d951
6106671529291317248

59
73
84.49272004157666
26.4332677307878
ce899bbc-e5da-4628-8b53-439dbbc9c894ce899bbc-e5da-4628-8b53-439dbbc9c894
1479129413091627008

98
51
38.189378995409044
66.0282166594723
b036df75-f457-4783-bf5a-2de6395baa34b036df75-f457-4783-bf5a-2de6395baa34b036df75-f457-4783-bf5a-2de6395baa34
8988141981055608832



SELECT 10


select sum(pg_column_size( bks.*       )) from t1 AS bks;
select sum(pg_column_size((a,b,c,d,e,f))) from t1 AS bks;


sum


52944



SELECT 1




sum


52944



SELECT 1


create or replace function column_sizes(target_table regclass) 
    returns table (column_name text, column_size bigint) 
    language plpgsql stable parallel restricted as $function$
declare 
    rec record;
    parsed_target_table text[]:=parse_ident(target_table::text);
    schema_name text:=case when parsed_target_table[2] is not null then
      parsed_target_table[1] end;
    table_name text:=parsed_target_table[array_upper(parsed_target_table,1)];
begin
    for rec in execute format(' select  column_name 
                                from    information_schema.columns 
                                where   (%1$L is null or table_schema = %1$L) 
                                and     (%2$L is null or table_name   = %2$L)',
                                schema_name,
                                table_name  ) loop
        return query execute format(
            'select %1$L,sum(pg_column_size(%1$I))::bigint from %s',
             rec.column_name,
             target_table);
    end loop;
end $function$;

CREATE FUNCTION


select * from column_sizes('public.t1');


column_name
column_size


a
600

b
1200

c
2400

d
3840

f
2400

e
33360



SELECT 6


select format('select pg_column_size(%I) from %s','abc','public.t1'::regclass);
alter table t1 set schema def;
select format('select pg_column_size(%I) from %s','abc','def.t1'::regclass);


format


select pg_column_size(abc) from t1



SELECT 1



ALTER TABLE




format


select pg_column_size(abc) from def.t1



SELECT 1

a	b	c	d	e	f
23	71	1.1721947768125185	16.7528977262813	faf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0adfaf7cfd8-7e57-4d8f-9f00-e984c438a0ad	2197393462332635136
72	29	39.01667117618446	93.9129676126963	a26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234ea26742a9-b10a-4d87-87a4-bdd84ce9234e	5833481516626774016
81	80	58.70090848363101	42.8630097743508	36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a36d0f0bd-e4c8-4b6a-bb3f-06c390f3f10a	3033801512388929536
97	33	86.72729390270628	7.83294175433189	104dfce4-96c7-40f4-ad91-e8098e09cfa3104dfce4-96c7-40f4-ad91-e8098e09cfa3104dfce4-96c7-40f4-ad91-e8098e09cfa3	4900319619640123392
24	76	50.437690834132695	85.1301868094659	877a91dd-7e79-4967-bc58-f90c5804d010877a91dd-7e79-4967-bc58-f90c5804d010	7976803432216248320
93	64	11.57090610979723	16.6931001402385	ccf7f439-02b7-419b-ba50-54356e6bafffccf7f439-02b7-419b-ba50-54356e6bafffccf7f439-02b7-419b-ba50-54356e6bafff	5839714898056380416
76	37	24.573926476861008	97.3345531773341	88b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd088b0719b-4486-449f-86ab-d1b923229fd0	495360858684590080
99	51	32.70351635877666	7.55685924071043	f3d3b592-43e9-412b-b313-15f81c23d951f3d3b592-43e9-412b-b313-15f81c23d951	6106671529291317248
59	73	84.49272004157666	26.4332677307878	ce899bbc-e5da-4628-8b53-439dbbc9c894ce899bbc-e5da-4628-8b53-439dbbc9c894	1479129413091627008
98	51	38.189378995409044	66.0282166594723	b036df75-f457-4783-bf5a-2de6395baa34b036df75-f457-4783-bf5a-2de6395baa34b036df75-f457-4783-bf5a-2de6395baa34	8988141981055608832