Postgres 10

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 mytable (
    id INTEGER PRIMARY KEY,
    data TEXT
);

CREATE TABLE

INSERT INTO mytable VALUES
    (0, 'a'),
    (1, 'b'),
    (2, 'c'),
    (3, 'd'),
    (4, 'e'),
    (5, 'f'),
    (6, 'g'),
    (7, 'h'),
    (8, 'i'),
    (9, 'j');

INSERT 0 10

select * from mytable

id
data

0
a

1
b

2
c

3
d

4
e

5
f

6
g

7
h

8
i

9
j

SELECT 10

delete from mytable where id < 3;

DELETE 3

insert into mytable values (2, 'c')

INSERT 0 1

insert into mytable values (1, 'b')

INSERT 0 1

insert into mytable values (0, 'a')

INSERT 0 1

select * from mytable

id
data

3
d

4
e

5
f

6
g

7
h

8
i

9
j

2
c

1
b

0
a

SELECT 10