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 type e as enum ('en', 'ru');
CREATE TABLE t1 (
c0 BIGINT DEFAULT 1,
c1 BIGINT DEFAULT 1,
c2 BIGINT DEFAULT 1,
c3 TIMESTAMP (0) WITH TIME ZONE DEFAULT clock_timestamp(),
c4 TIMESTAMP (0) WITH TIME ZONE DEFAULT clock_timestamp(),
c5 INTEGER DEFAULT 1,
c6 e DEFAULT 'en',
c7 SMALLINT DEFAULT 1,
c8 NUMERIC(16, 4) DEFAULT 1111.111,
c9 NUMERIC(16, 4) DEFAULT 1111.111,
c10 NUMERIC(16, 4) DEFAULT 1111.111,
c11 NUMERIC(16, 4) DEFAULT 1111.111,
c12 VARCHAR(300) DEFAULT 'en',
c13 VARCHAR(300) DEFAULT 'en',
c14 VARCHAR(100) DEFAULT 'en',
c15 BOOLEAN DEFAULT TRUE,
c16 BOOLEAN DEFAULT TRUE
);
INSERT INTO t1 VALUES (DEFAULT);
1 rows affected
CREATE TABLE t2 (
c7 SMALLINT DEFAULT 1,
c16 BOOLEAN DEFAULT TRUE,
c15 BOOLEAN DEFAULT TRUE,
c11 NUMERIC(16, 4) DEFAULT 1111.111,
c13 VARCHAR(300) DEFAULT 'en',
c9 NUMERIC(16, 4) DEFAULT 1111.111,
c8 NUMERIC(16, 4) DEFAULT 1111.111,
c10 NUMERIC(16, 4) DEFAULT 1111.111,
c6 e DEFAULT 'en',
c5 INTEGER DEFAULT 1,
c14 VARCHAR(100) DEFAULT 'en',
c12 VARCHAR(300) DEFAULT 'en',
c4 TIMESTAMP (0) WITH TIME ZONE DEFAULT clock_timestamp(),
c3 TIMESTAMP (0) WITH TIME ZONE DEFAULT clock_timestamp(),
c0 BIGINT DEFAULT 1,
c1 BIGINT DEFAULT 1,
c2 BIGINT DEFAULT 1
);
INSERT INTO t2 values(DEFAULT);
1 rows affected
select * from t1;
| c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11 | c12 | c13 | c14 | c15 | c16 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2020-09-23 04:09:27+01 | 2020-09-23 04:09:27+01 | 1 | en | 1 | 1111.1110 | 1111.1110 | 1111.1110 | 1111.1110 | en | en | en | t | t |
select * from t2;
| c7 | c16 | c15 | c11 | c13 | c9 | c8 | c10 | c6 | c5 | c14 | c12 | c4 | c3 | c0 | c1 | c2 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | t | t | 1111.1110 | en | 1111.1110 | 1111.1110 | 1111.1110 | en | 1 | en | en | 2020-09-23 04:09:27+01 | 2020-09-23 04:09:27+01 | 1 | 1 | 1 |
SELECT pg_column_size(t1) as t1 FROM t1;
| t1 |
|---|
| 113 |
SELECT pg_column_size(t2) as t2 FROM t2;
| t2 |
|---|
| 120 |