Postgres 12

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 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE test
(
  lpo_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
  lang_1 INTEGER NOT NULL,
  lang_2 INTEGER NOT NULL,
  
  CONSTRAINT l1_ne_l2_ck CHECK (lang_1 != lang_2)
);
INSERT INTO test (lang_1, lang_2) VALUES
(6, 4),
(4, 6),
(6, 68),
(68, 6),
(18, 6),
(6, 18),
(36, 18),
(18, 36),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 7),
(1, 8),

(2, 3),
(2, 4),
(2, 5),
(2, 6),
(2, 7),
(2, 8);
21 rows affected

SELECT 
  lpo_id,
  LEAST(lang_1, lang_2), 
  GREATEST(lang_1, lang_2)
FROM test;


lpo_id
least
greatest


1
4
6

2
4
6

3
6
68

4
6
68

5
6
18

6
6
18

7
18
36

8
18
36

9
1
2

10
1
3

11
1
4

12
1
5

13
1
6

14
1
7

15
1
8

16
2
3

17
2
4

18
2
5

19
2
6

20
2
7

21
2
8


SELECT 
  lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
FROM test
ORDER BY lpo_id;


lpo_id
least
greatest
rn


1
4
6
2

2
4
6
1

3
6
68
2

4
6
68
1

5
6
18
2

6
6
18
1

7
18
36
2

8
18
36
1

9
1
2
1

10
1
3
1

11
1
4
1

12
1
5
1

13
1
6
1

14
1
7
1

15
1
8
1

16
2
3
1

17
2
4
1

18
2
5
1

19
2
6
1

20
2
7
1

21
2
8
1


WITH cte AS
(
  SELECT lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
  FROM test
)
SELECT * FROM cte
ORDER BY lpo_id;


lpo_id
least
greatest
rn


1
4
6
2

2
4
6
1

3
6
68
2

4
6
68
1

5
6
18
2

6
6
18
1

7
18
36
2

8
18
36
1

9
1
2
1

10
1
3
1

11
1
4
1

12
1
5
1

13
1
6
1

14
1
7
1

15
1
8
1

16
2
3
1

17
2
4
1

18
2
5
1

19
2
6
1

20
2
7
1

21
2
8
1


WITH cte AS
(
  SELECT lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(lang_1, lang_2), GREATEST(lang_1, lang_2)) AS rn
  FROM test
)
DELETE FROM test t 
WHERE t.lpo_id IN 
(
  SELECT lpo_id FROM cte
  WHERE rn > 1
);
4 rows affected

SELECT 
  lpo_id,
  LEAST(lang_1, lang_2),
  GREATEST(lang_1, lang_2)
FROM test ORDER BY lpo_id;


lpo_id
least
greatest


2
4
6

4
6
68

6
6
18

8
18
36

9
1
2

10
1
3

11
1
4

12
1
5

13
1
6

14
1
7

15
1
8

16
2
3

17
2
4

18
2
5

19
2
6

20
2
7

21
2
8

lpo_id	least	greatest
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest
2	4	6
4	6	68
6	6	18
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	least	greatest
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest
2	4	6
4	6	68
6	6	18
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	least	greatest
1	4	6
2	4	6
3	6	68
4	6	68
5	6	18
6	6	18
7	18	36
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest	rn
1	4	6	2
2	4	6	1
3	6	68	2
4	6	68	1
5	6	18	2
6	6	18	1
7	18	36	2
8	18	36	1
9	1	2	1
10	1	3	1
11	1	4	1
12	1	5	1
13	1	6	1
14	1	7	1
15	1	8	1
16	2	3	1
17	2	4	1
18	2	5	1
19	2	6	1
20	2	7	1
21	2	8	1

lpo_id	least	greatest
2	4	6
4	6	68
6	6	18
8	18	36
9	1	2
10	1	3
11	1	4
12	1	5
13	1	6
14	1	7
15	1	8
16	2	3
17	2	4
18	2	5
19	2	6
20	2	7
21	2	8