Postgres 9.3

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 9.3.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


create table test (id integer, name text);

CREATE TABLE


insert into test values (1,'name-1');
insert into test values (1,'name-10');
insert into test values (1,'name-100');
insert into test values (1,'name-1000');
insert into test values (1,'name-10000');
insert into test values (1,'name-100000');
insert into test values (1,'name-1000000');
insert into test values (2,'name-2');
insert into test values (2,'name-20');
insert into test values (2,'name-200');
insert into test values (2,'name-2000');
insert into test values (2,'name-20000');
insert into test values (3,'name-3');
insert into test values (3,'name-30');
insert into test values (3,'name-300');
insert into test values (3,'name-3000');
insert into test values (3,'name-30000');
insert into test values (3,'name-300000');
insert into test values (3,'name-3000000');
insert into test values (4,'name-4');
insert into test values (4,'name-40');
insert into test values (4,'name-400');

INSERT 0 1


with data as (
select row_number() over (partition by id order by random()) rn,
* from test)
select rn,id,name from data 
where rn<=3 order by id;


rn
id
name


1
1
name-1000000

2
1
name-10000

3
1
name-100

1
2
name-2

2
2
name-20000

3
2
name-200

1
3
name-3000

2
3
name-3

3
3
name-300

1
4
name-400

2
4
name-4

3
4
name-40



SELECT 12

rn	id	name
1	1	name-1000000
2	1	name-10000
3	1	name-100
1	2	name-2
2	2	name-20000
3	2	name-200
1	3	name-3000
2	3	name-3
3	3	name-300
1	4	name-400
2	4	name-4
3	4	name-40