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 t as 
select id, 'ROW'||MOD(id,7) as s FROM 
generate_series(1,100) as id;

SELECT 100


select * from t;


id
s


1
ROW1

2
ROW2

3
ROW3

4
ROW4

5
ROW5

6
ROW6

7
ROW0

8
ROW1

9
ROW2

10
ROW3

11
ROW4

12
ROW5

13
ROW6

14
ROW0

15
ROW1

16
ROW2

17
ROW3

18
ROW4

19
ROW5

20
ROW6

21
ROW0

22
ROW1

23
ROW2

24
ROW3

25
ROW4

26
ROW5

27
ROW6

28
ROW0

29
ROW1

30
ROW2

31
ROW3

32
ROW4

33
ROW5

34
ROW6

35
ROW0

36
ROW1

37
ROW2

38
ROW3

39
ROW4

40
ROW5

41
ROW6

42
ROW0

43
ROW1

44
ROW2

45
ROW3

46
ROW4

47
ROW5

48
ROW6

49
ROW0

50
ROW1

51
ROW2

52
ROW3

53
ROW4

54
ROW5

55
ROW6

56
ROW0

57
ROW1

58
ROW2

59
ROW3

60
ROW4

61
ROW5

62
ROW6

63
ROW0

64
ROW1

65
ROW2

66
ROW3

67
ROW4

68
ROW5

69
ROW6

70
ROW0

71
ROW1

72
ROW2

73
ROW3

74
ROW4

75
ROW5

76
ROW6

77
ROW0

78
ROW1

79
ROW2

80
ROW3

81
ROW4

82
ROW5

83
ROW6

84
ROW0

85
ROW1

86
ROW2

87
ROW3

88
ROW4

89
ROW5

90
ROW6

91
ROW0

92
ROW1

93
ROW2

94
ROW3

95
ROW4

96
ROW5

97
ROW6

98
ROW0

99
ROW1

100
ROW2



SELECT 100


 WITH d 
         AS (SELECT ctid c, 
                    row_number() 
                      OVER ( 
                        partition BY s 
                        ORDER BY id desc) rn 
             FROM   t) 
    DELETE FROM t 
    WHERE  ctid IN (SELECT c 
                   FROM   d 
                   WHERE  rn > 1)  ; 

DELETE 93


select * from t order by id;


id
s


94
ROW3

95
ROW4

96
ROW5

97
ROW6

98
ROW0

99
ROW1

100
ROW2



SELECT 7

id	s
1	ROW1
2	ROW2
3	ROW3
4	ROW4
5	ROW5
6	ROW6
7	ROW0
8	ROW1
9	ROW2
10	ROW3
11	ROW4
12	ROW5
13	ROW6
14	ROW0
15	ROW1
16	ROW2
17	ROW3
18	ROW4
19	ROW5
20	ROW6
21	ROW0
22	ROW1
23	ROW2
24	ROW3
25	ROW4
26	ROW5
27	ROW6
28	ROW0
29	ROW1
30	ROW2
31	ROW3
32	ROW4
33	ROW5
34	ROW6
35	ROW0
36	ROW1
37	ROW2
38	ROW3
39	ROW4
40	ROW5
41	ROW6
42	ROW0
43	ROW1
44	ROW2
45	ROW3
46	ROW4
47	ROW5
48	ROW6
49	ROW0
50	ROW1
51	ROW2
52	ROW3
53	ROW4
54	ROW5
55	ROW6
56	ROW0
57	ROW1
58	ROW2
59	ROW3
60	ROW4
61	ROW5
62	ROW6
63	ROW0
64	ROW1
65	ROW2
66	ROW3
67	ROW4
68	ROW5
69	ROW6
70	ROW0
71	ROW1
72	ROW2
73	ROW3
74	ROW4
75	ROW5
76	ROW6
77	ROW0
78	ROW1
79	ROW2
80	ROW3
81	ROW4
82	ROW5
83	ROW6
84	ROW0
85	ROW1
86	ROW2
87	ROW3
88	ROW4
89	ROW5
90	ROW6
91	ROW0
92	ROW1
93	ROW2
94	ROW3
95	ROW4
96	ROW5
97	ROW6
98	ROW0
99	ROW1
100	ROW2