add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 table test(
created_at date default now()-random()*'35 years'::interval
);
do $d$
declare columns_ int:=10; i int;
begin
for i in 1..columns_ loop
execute format('alter table test
add column c_%1$s text
default repeat(md5(random()::text)::text,(random()*3)::int)
,alter column c_%1$s set storage external ;',i::text);
end loop;
end $d$;

select*
from information_schema.columns
where table_name='test';
CREATE TABLE

DO
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
db_1791768529 public test created_at 1 (now() - (random() * '35 years'::interval)) YES date null null null null null 0 null null null null null null null null null null null db_1791768529 pg_catalog date null null null null 1 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_1 2 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 2 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_2 3 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 3 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_3 4 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 4 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_4 5 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 5 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_5 6 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 6 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_6 7 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 7 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_7 8 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 8 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_8 9 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 9 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_9 10 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 10 NO NO null null null null null NO NEVER null YES
db_1791768529 public test c_10 11 repeat(md5((random())::text), ((random() * (3)::double precision))::integer) YES text null 1073741824 null null null null null null null null null null null null null null null db_1791768529 pg_catalog text null null null null 11 NO NO null null null null null NO NEVER null YES 
SELECT 11
select setseed(.42);

insert into test
select from generate_series(1,1e4);

update test
set c_5=repeat(c_5,-10*(1985-date_part('year',created_at))::int);

select created_at
,-10*(1985-date_part('year',created_at))::int as c_5_text_multiplied_by
,pg_column_size(c_5)
from test
limit 4;
setseed 
SELECT 1

INSERT 0 10000

UPDATE 10000
created_at c_5_text_multiplied_by pg_column_size
1994-07-07 90 2880
1992-08-25 70 2240
2023-08-02 380 24320
2011-04-17 260 16640 
SELECT 4
do $d$
declare v_column text;
query_body text:=
$q$create table results as
select date_part('year', created_at)
-date_part('year', created_at)::int % 5 as year
,sum( $q$;
begin
for v_column in select column_name
from information_schema.columns
where table_schema='public'
and table_name='test'
and column_name<>'created_at'
loop query_body:= format(E'%s\n\t\t\t +pg_column_size(%I)',query_body,v_column);
end loop;
query_body:= query_body||E'
) as size
from test
group by 1
order by 1;';
execute query_body;
create table debug_ as select query_body;
end $d$;

table debug_;

select year
,pg_size_pretty(size)
from results;
DO
query_body
create table results as
select date_part('year', created_at)
       -date_part('year', created_at)::int % 5 as year
      ,sum(
+pg_column_size(c_1)
+pg_column_size(c_2)
+pg_column_size(c_3)
+pg_column_size(c_4)
+pg_column_size(c_5)
+pg_column_size(c_6)
+pg_column_size(c_7)
+pg_column_size(c_8)
+pg_column_size(c_9)
+pg_column_size(c_10)
       ) as size
from test
group by 1
order by 1;		 
SELECT 1
year pg_size_pretty
1985 99 kB
1990 5353 kB
1995 8554 kB
2000 12 MB
2005 16 MB
2010 18 MB
2015 21 MB
2020 24 MB 
SELECT 8
select date_part('year', created_at)
-date_part('year', created_at)::int % 5 as year
,sum(
+pg_column_size(c_1)
+pg_column_size(c_2)
+pg_column_size(c_3)
+pg_column_size(c_4)
+pg_column_size(c_5)
+pg_column_size(c_6)
+pg_column_size(c_7)
+pg_column_size(c_8)
+pg_column_size(c_9)
+pg_column_size(c_10)
) as size
from test
group by 1
order by 1;
year size
1985 101006
1990 5481437
1995 8759566
2000 12072367
2005 16254968
2010 19064089
2015 22478199
2020 24930059 
SELECT 8
select date_part('year', created_at) - date_part('year', created_at)::int % 5
as year
,pg_size_pretty(sum(pg_column_size(test)))
from test
group by 1
order by 1;
year pg_size_pretty
1985 100 kB
1990 5396 kB
1995 8598 kB
2000 12 MB
2005 16 MB
2010 18 MB
2015 21 MB
2020 24 MB 
SELECT 8