Postgres 15

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 x as select from generate_series(1,4);
create table y()inherits(x);
insert into y select from generate_series(1,4);

SELECT 4

CREATE TABLE

INSERT 0 4

begin;
delete from x where ctid in(select ctid from x limit 2)
returning tableoid::regclass,ctid;
select count(*) from only x;
select count(*) from only y;
rollback;

BEGIN

tableoid
ctid

x
(0,1)

y
(0,1)

x
(0,2)

y
(0,2)

DELETE 4

count

2

SELECT 1

count

2

SELECT 1

ROLLBACK

begin;
delete from only x where ctid in(select ctid from x limit 2)
returning tableoid::regclass,ctid;
select count(*) from only x;
select count(*) from only y;
rollback;

BEGIN

tableoid
ctid

x
(0,1)

x
(0,2)

DELETE 2

count

2

SELECT 1

count

4

SELECT 1

ROLLBACK

begin;
delete from x where (tableoid,ctid) in(select tableoid,ctid from x limit 2)
returning tableoid::regclass,ctid;
select count(*) from only x;
select count(*) from only y;
rollback

BEGIN

tableoid
ctid

x
(0,1)

x
(0,2)

DELETE 2

count

2

SELECT 1

count

4

SELECT 1

ROLLBACK

select setseed(.42);

create table z(c int)partition by list(c);
create table z0 partition of z default;
create table z1 partition of z for values in (1);

insert into z select random()*2 from generate_series(1,4)
returning tableoid::regclass,ctid,c;

setseed

SELECT 1

CREATE TABLE

CREATE TABLE

CREATE TABLE

tableoid
ctid
c

z1
(0,1)
1

z1
(0,2)
1

z0
(0,1)
2

z0
(0,2)
0

INSERT 0 4

begin;
delete from only z where ctid in(select ctid from z limit 2)
returning tableoid::regclass,ctid,c;
rollback

BEGIN

tableoid
ctid
c

DELETE 0

ROLLBACK

begin;
delete from z where ctid in(select ctid from z limit 2)
returning tableoid::regclass,ctid,c;
rollback

BEGIN

tableoid
ctid
c

z1
(0,1)
1

z0
(0,1)
2

z1
(0,2)
1

z0
(0,2)
0

DELETE 4

ROLLBACK

begin;
delete from z where (tableoid,ctid) in(select tableoid,ctid from z limit 2)
returning tableoid::regclass,ctid,c;
rollback

BEGIN

tableoid
ctid
c

z1
(0,1)
1

z1
(0,2)
1

DELETE 2

ROLLBACK