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
, storage "char"
, col text
, CONSTRAINT tbl_col_uni UNIQUE (col)
);

-- Also tested with the same result, as expected:
-- - UNIQUE index
-- - plain btree index
-- - PK constraint.

CREATE TABLE


-- default for text is EXTENDED
INSERT INTO tbl VALUES
(111, 'x', rpad(md5(random()::text), 10000, md5(random()::text)));  -- works

INSERT INTO tbl VALUES
(112, 'x', rpad(md5(random()::text),  8000, md5(random()::text)));  -- works


ALTER TABLE tbl ALTER col SET STORAGE PLAIN;                        -- no compression, no toast

--INSERT INTO tbl VALUES
-- (121, 'p', rpad(md5(random()::text), 10000, md5(random()::text)));  -- 10000 = too big for "plain" storage anywhere with 8k page size
-- ERROR:  row is too big: size 10040, maximum size 8160

INSERT INTO tbl VALUES
(122, 'p', rpad(md5(random()::text),  8000, md5(random()::text)));  -- works


ALTER TABLE tbl ALTER col SET STORAGE EXTERNAL;                     -- no compression

INSERT INTO tbl VALUES
(131, 'e', rpad(md5(random()::text), 10000, md5(random()::text)));

INSERT INTO tbl VALUES
(132, 'e', rpad(md5(random()::text),  8000, md5(random()::text)));


ALTER TABLE tbl ALTER col SET STORAGE MAIN;                         -- toast = only last restort

INSERT INTO tbl VALUES
(141, 'm', rpad(md5(random()::text), 10000, md5(random()::text)));

INSERT INTO tbl VALUES
(142, 'm', rpad(md5(random()::text),  8000, md5(random()::text)));


ALTER TABLE tbl ALTER col SET STORAGE EXTENDED;                     -- return to default with compression & toast

INSERT 0 1



INSERT 0 1



ALTER TABLE



ERROR:  index row size 8016 exceeds maximum 2712 for index "tbl_col_uni"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.


-- Error case
ALTER TABLE tbl ALTER col SET STORAGE PLAIN;                        -- no compression, no toast

INSERT INTO tbl VALUES
(121, 'p', rpad(md5(random()::text), 10000, md5(random()::text)));  -- 10000 = too big for "plain" storage anywhere with 8k page size
-- ERROR:  row is too big: size 10040, maximum size 8160

ALTER TABLE



ERROR:  row is too big: size 10040, maximum size 8160


-- Test INDEX itself 
UPDATE pg_attribute SET attstorage = 'p'
WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col';       -- hack idx col setting is sys catalog to PLAIN

-- INSERT INTO tbl VALUES
-- (221, 'p', rpad(md5(random()::text), 10000, md5(random()::text)));
-- ERROR:  index row requires 10016 bytes, maximum size is 8191

INSERT INTO tbl VALUES
(222, 'p', rpad(md5(random()::text),  8000, md5(random()::text)));
-- ERROR:  index row size 8016 exceeds maximum 2712 for index "tbl_col_uni"
-- HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
-- Consider a function index of an MD5 hash of the value, or use full text indexing.


UPDATE pg_attribute SET attstorage = 'e'
WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col';        -- hack idx col setting to EXTERNAL

-- INSERT INTO tbl VALUES
-- (231, 'e', rpad(md5(random()::text), 10000, md5(random()::text)));
-- ERROR:  index row requires 10016 bytes, maximum size is 8191

-- INSERT INTO tbl VALUES
-- (232, 'e', rpad(md5(random()::text),  8000, md5(random()::text)));
-- ERROR:  index row size 8016 exceeds maximum 2712 for index "tbl_col_uni" ...


UPDATE pg_attribute SET attstorage = 'm'
WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'col';       -- hack idx col setting to MAIN;

INSERT INTO tbl VALUES
(241, 'm', rpad(md5(random()::text), 10000, md5(random()::text)));  -- works

INSERT INTO tbl VALUES
(242, 'm', rpad(md5(random()::text),  8000, md5(random()::text)));  -- works

ERROR:  permission denied for table pg_attribute


-- Error case 221
UPDATE pg_attribute SET attstorage = 'p' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt';    -- hack idx col setting is sys catalog to PLAIN
INSERT INTO tbl VALUES (221, 'p', rpad(md5(random()::text), 10000, md5(random()::text)));

ERROR:  permission denied for table pg_attribute


-- Error case 222
UPDATE pg_attribute SET attstorage = 'p' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt';    -- hack idx col setting is sys catalog to PLAIN
INSERT INTO tbl VALUES (222, 'p', rpad(md5(random()::text),  8000, md5(random()::text)));

ERROR:  permission denied for table pg_attribute


-- Error case 231
UPDATE pg_attribute SET attstorage = 'e' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt';     -- hack idx col setting to EXTERNAL
INSERT INTO tbl VALUES (231, 'e', rpad(md5(random()::text), 10000, md5(random()::text)));

ERROR:  permission denied for table pg_attribute


-- Error case 232
UPDATE pg_attribute SET attstorage = 'e' WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt';     -- hack idx col setting to EXTERNAL
INSERT INTO tbl VALUES (232, 'e', rpad(md5(random()::text),  8000, md5(random()::text)));

ERROR:  permission denied for table pg_attribute


-- Index never breaks  in any case!
-- UPDATE pg_attribute SET attstorage = 'x'
-- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default
INSERT INTO tbl VALUES (311, 'm', rpad(md5('non_random'::text), 10000, md5('non_random'::text)));
INSERT INTO tbl VALUES (311, 'm', rpad(md5('non_random'::text), 10000, md5('non_random'::text)));
-- ERROR:  duplicate key value violates unique constraint "idxmax_txt_uni"
-- DETAIL:  Key (txt)=(67ad0f29faa14a2d6183779 ...

INSERT 0 1



ERROR:  duplicate key value violates unique constraint "tbl_col_uni"
DETAIL:  Key (col)=(67ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d61837791e6640ba467ad0f29faa14a2d) already exists.


-- Even 100000 characters work (hint: repeating pattern -> good compression)
-- UPDATE pg_attribute SET attstorage = 'x'
-- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default
INSERT INTO tbl VALUES
(411, 'x', rpad(md5('non_random'::text), 100000, md5('non_random'::text)));

INSERT 0 1


-- Too much is too much, finally, we exceed the index row size:
-- UPDATE pg_attribute SET attstorage = 'x'
-- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default
INSERT INTO tbl VALUES
(511, 'x', rpad(md5('non_random'::text), 500000, md5('non_random'::text)));
-- ERROR:  index row size 5784 exceeds maximum 2712 for index "tbl_col_uni"

ERROR:  index row size 5784 exceeds maximum 2712 for index "tbl_col_uni"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.


--.. and the page size
-- UPDATE pg_attribute SET attstorage = 'x'
-- WHERE attrelid = 'tbl_col_uni'::regclass AND attname = 'txt'; -- reset to default
INSERT INTO tbl VALUES (611, 'x', rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));
-- ERROR:  index row requires 11504 bytes, maximum size is 8191

ERROR:  index row requires 11504 bytes, maximum size is 8191


SELECT id, storage
     , left(col, 15), length(col), pg_column_size(col)
FROM   tbl ORDER BY id;


id
storage
left
length
pg_column_size


411
x
67ad0f29faa14a2
100000
1191



SELECT 1