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
);
INSERT INTO mytable(step_id,doc2) VALUES (1,'same size');
INSERT INTO mytable(step_id,doc2) VALUES (1,'same size');
INSERT INTO mytable(step_id,doc2) VALUES (10580774,'XXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580774,'XXXXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580775,'XX');
INSERT INTO mytable(step_id,doc2) VALUES (10580775,'XXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580776,'XXXXX');
INSERT INTO mytable(step_id,doc2) VALUES (10580776,'XX');
INSERT INTO mytable(step_id,doc2) VALUES (10580777,'X');
INSERT INTO mytable(step_id,doc2) VALUES (10580777,'XXXX');
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 tempid SERIAL
select *, length(doc2) FROM mytable


step_id
doc2
tempid
length


1
same size
1
9

1
same size
2
9

10580774
XXXX
3
4

10580774
XXXXXX
4
6

10580775
XX
5
2

10580775
XXXX
6
4

10580776
XXXXX
7
5

10580776
XX
8
2

10580777
X
9
1

10580777
XXXX
10
4


/* ABOUT TO DELETE THESE ROWS */
with CTE as (
select
*
, row_number() over(partition by step_id order by length(doc2) DESC) as rn
from mytable
)
select
*
from mytable t
where exists (select NULL
              from CTE
              where t.tempid = CTE.tempid and CTE.rn > 1)


step_id
doc2
tempid


1
same size
2

10580774
XXXX
3

10580775
XX
5

10580776
XX
8

10580777
X
9


/* ACTUALLY DELETE THE ROWS */
with CTE as (
select
*
, row_number() over(partition by step_id order by length(doc2) DESC) as rn
from mytable
)
delete
from mytable t
where exists (select NULL
              from CTE
              where t.tempid = CTE.tempid and CTE.rn > 1)
5 rows affected

select
*, length(doc2)
from mytable


step_id
doc2
tempid
length


1
same size
1
9

10580774
XXXXXX
4
6

10580775
XXXX
6
4

10580776
XXXXX
7
5

10580777
XXXX
10
4


ALTER TABLE mytable DROP COLUMN tempid
select
*, length(doc2)
from mytable


step_id
doc2
length


1
same size
9

10580774
XXXXXX
6

10580775
XXXX
4

10580776
XXXXX
5

10580777
XXXX
4

step_id	doc2	tempid	length
1	same size	1	9
1	same size	2	9
10580774	XXXX	3	4
10580774	XXXXXX	4	6
10580775	XX	5	2
10580775	XXXX	6	4
10580776	XXXXX	7	5
10580776	XX	8	2
10580777	X	9	1
10580777	XXXX	10	4