Postgres 12

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?.

select version();

version

PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation

read committed

CREATE TABLE x (y INT, z VARCHAR (25));
INSERT INTO x VALUES (1, 'One'), (2, 'Two'), (3, 'Three'); 
3 rows affected

SELECT * FROM x;

y
z

1
One

2
Two

3
Three

START TRANSACTION;

SELECT CURRENT_TIMESTAMP(6);

SELECT * FROM x;

SELECT CURRENT_TIMESTAMP(6);

COMMIT;

current_timestamp

2021-05-13 15:51:11.194766+01

y
z

1
One

2
Two

3
Three

current_timestamp

2021-05-13 15:51:11.194766+01

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

SELECT CURRENT_TIMESTAMP(6);

SELECT * FROM x;

SELECT CURRENT_TIMESTAMP(6);

COMMIT;

current_timestamp

2021-05-13 15:51:11.195218+01

y
z

1
One

2
Two

3
Three

current_timestamp

2021-05-13 15:51:11.195218+01