Postgres 9.6

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(
   step_id  INTEGER  NOT NULL
  ,doc2     VARCHAR(11) NOT NULL
  ,doc2size INTEGER  NOT NULL
);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (1,'same size',24);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (1,'same size',24);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580774,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580774,'369',369);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580775,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580775,'379',379);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580776,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580776,'373',373);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580777,'318',318);
INSERT INTO mytable(step_id,doc2,doc2size) VALUES (10580777,'370',370);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

 ALTER TABLE mytable ADD COLUMN id SERIAL
with CTE as (
select
*
, row_number() over(partition by step_id order by doc2size DESC) as rn
from mytable
)
select 
*
from mytable t
left join CTE on t.id = CTE.id and CTE.rn = 1
where CTE.id IS NULL


step_id
doc2
doc2size
id
step_id
doc2
doc2size
id
rn


1
same size
24
2
null
null
null
null
null

10580774
318
318
3
null
null
null
null
null

10580775
318
318
5
null
null
null
null
null

10580776
318
318
7
null
null
null
null
null

10580777
318
318
9
null
null
null
null
null


with CTE as (
select
*
, row_number() over(partition by step_id order by doc2size DESC) as rn
from mytable
)
delete
from mytable t
where exists (select NULL
              from CTE
              where t.id = CTE.id and CTE.rn > 1)
     
5 rows affected

select
*
from mytable


step_id
doc2
doc2size
id


1
same size
24
1

10580774
369
369
4

10580775
379
379
6

10580776
373
373
8

10580777
370
370
10

step_id	doc2	doc2size	id	step_id	doc2	doc2size	id	rn
1	same size	24	2	null	null	null	null	null
10580774	318	318	3	null	null	null	null	null
10580775	318	318	5	null	null	null	null	null
10580776	318	318	7	null	null	null	null	null
10580777	318	318	9	null	null	null	null	null