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

with CTE as (
select
*
, row_number() over(partition by step_id order by doc2size DESC) as rn
from mytable
)
select 
*
from mytable t
where exists (
     select null
     from CTE 
     where t.step_id = cte.step_id
     and t.doc2 = cte.doc2
     and CTE.rn > 1)
     


step_id
doc2
doc2size


1
same size
24

1
same size
24

10580774
318
318

10580775
318
318

10580776
318
318

10580777
318
318


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.step_id = cte.step_id
     and t.doc2 = cte.doc2
     and CTE.rn > 1)
     
6 rows affected

select
*
from mytable


step_id
doc2
doc2size


10580774
369
369

10580775
379
379

10580776
373
373

10580777
370
370

step_id	doc2	doc2size
1	same size	24
1	same size	24
10580774	318	318
10580775	318	318
10580776	318	318
10580777	318	318