Postgres 11

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 tbl (id int, col text);
INSERT INTO tbl(id, col) VALUES 
   (1, rpad(md5('non_random'::text),     100, md5('non_random'::text)))
 , (2, rpad(md5('non_random'::text),    1000, md5('non_random'::text)))
 , (3, rpad(md5('non_random'::text),   10000, md5('non_random'::text)))
 , (4, rpad(md5('non_random'::text),  100000, md5('non_random'::text)))
 , (5, rpad(md5('non_random'::text),  500000, md5('non_random'::text)))
 , (6, rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));
6 rows affected

SELECT id, left(col, 10) || ' ...' AS col
     , length(col) AS char_length
     , pg_column_size(col) AS compressed
     , pg_column_size(col || '') AS uncompressed
FROM   tbl ORDER BY id;


id
col
char_length
compressed
uncompressed


1
67ad0f29fa ...
100
101
104

2
67ad0f29fa ...
1000
1004
1004

3
67ad0f29fa ...
10000
160
10004

4
67ad0f29fa ...
100000
1191
100004

5
67ad0f29fa ...
500000
5765
500004

6
67ad0f29fa ...
1000000
11487
1000004


SELECT pg_column_size(rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));


pg_column_size


1000004

id	col	char_length	compressed	uncompressed
1	67ad0f29fa ...	100	101	104
2	67ad0f29fa ...	1000	1004	1004
3	67ad0f29fa ...	10000	160	10004
4	67ad0f29fa ...	100000	1191	100004
5	67ad0f29fa ...	500000	5765	500004
6	67ad0f29fa ...	1000000	11487	1000004