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 PRIMARY KEY,
  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, lang_1, lang_2,
  LEAST(lang_1, lang_2), 
  GREATEST(lang_1, lang_2)
FROM test;


lpo_id
lang_1
lang_2
least
greatest


1
6
4
4
6

2
4
6
4
6

3
6
68
6
68

4
68
6
6
68

5
18
6
6
18

6
6
18
6
18

7
36
18
18
36

8
18
36
18
36

9
1
2
1
2

10
1
3
1
3

11
1
4
1
4

12
1
5
1
5

13
1
6
1
6

14
1
7
1
7

15
1
8
1
8

16
2
3
2
3

17
2
4
2
4

18
2
5
2
5

19
2
6
2
6

20
2
7
2
7

21
2
8
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 t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
)
DELETE t
FROM test t
JOIN cte
     ON t.lpo_id = cte.lpo_id
     AND cte.rn > 1;
ERROR:  syntax error at or near "t"
LINE 10: DELETE t
                ^


DELETE t
FROM test t
JOIN 
(
  SELECT t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
)
  AS cte
     ON t.lpo_id = cte.lpo_id
     AND cte.rn > 1;
ERROR:  syntax error at or near "t"
LINE 2: DELETE t
               ^


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


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


EXPlAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE t
FROM test t
JOIN 
(
  SELECT t.lpo_id,
  LEAST(t.lang_1, t.lang_2),
  GREATEST(t.lang_1, t.lang_2),
  ROW_NUMBER() 
    OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
  FROM test t
) AS tab
ON t.lpo_id = tab.lpo_id
AND tab.rn > 1;
ERROR:  syntax error at or near "t"
LINE 2: DELETE t
               ^


EXPlAIN (ANALYZE, BUFFERS, VERBOSE)
DELETE FROM test
WHERE test.lpo_id IN
(
  SELECT tab.lpo_id FROM
  (
    SELECT 
      t.lpo_id, LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2), 
      ROW_NUMBER() OVER (PARTITION BY LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2)) AS rn
    FROM test t ORDER BY t.lpo_id
  ) AS tab
  WHERE tab.rn > 1
);


QUERY PLAN


Delete on public.test  (cost=352.62..395.94 rows=680 width=34) (actual time=0.153..0.155 rows=0 loops=1)

  Buffers: shared hit=6

  ->  Hash Join  (cost=352.62..395.94 rows=680 width=34) (actual time=0.133..0.140 rows=4 loops=1)

        Output: test.ctid, tab.*

        Inner Unique: true

        Hash Cond: (test.lpo_id = tab.lpo_id)

        Buffers: shared hit=2

        ->  Seq Scan on public.test  (cost=0.00..30.40 rows=2040 width=10) (actual time=0.007..0.010 rows=21 loops=1)

              Output: test.ctid, test.lpo_id

              Buffers: shared hit=1

        ->  Hash  (cost=350.15..350.15 rows=197 width=32) (actual time=0.105..0.106 rows=4 loops=1)

              Output: tab.*, tab.lpo_id

              Buckets: 1024  Batches: 1  Memory Usage: 9kB

              Buffers: shared hit=1

              ->  HashAggregate  (cost=348.18..350.15 rows=197 width=32) (actual time=0.092..0.095 rows=4 loops=1)

                    Output: tab.*, tab.lpo_id

                    Group Key: tab.lpo_id

                    Buffers: shared hit=1

                    ->  Subquery Scan on tab  (cost=315.88..346.48 rows=680 width=32) (actual time=0.083..0.088 rows=4 loops=1)

                          Output: tab.*, tab.lpo_id

                          Filter: (tab.rn > 1)

                          Rows Removed by Filter: 17

                          Buffers: shared hit=1

                          ->  Sort  (cost=315.88..320.98 rows=2040 width=20) (actual time=0.061..0.063 rows=21 loops=1)

                                Output: t.lpo_id, (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), (row_number() OVER (?))

                                Sort Key: t.lpo_id

                                Sort Method: quicksort  Memory: 26kB

                                Buffers: shared hit=1

                                ->  WindowAgg  (cost=152.74..203.74 rows=2040 width=20) (actual time=0.032..0.046 rows=21 loops=1)

                                      Output: t.lpo_id, (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), row_number() OVER (?)

                                      Buffers: shared hit=1

                                      ->  Sort  (cost=152.74..157.84 rows=2040 width=12) (actual time=0.023..0.025 rows=21 loops=1)

                                            Output: (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), t.lpo_id

                                            Sort Key: (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2))

                                            Sort Method: quicksort  Memory: 25kB

                                            Buffers: shared hit=1

                                            ->  Seq Scan on public.test t  (cost=0.00..40.60 rows=2040 width=12) (actual time=0.005..0.008 rows=21 loops=1)

                                                  Output: LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2), t.lpo_id

                                                  Buffers: shared hit=1

Planning Time: 0.205 ms

Execution Time: 0.274 ms


EXPlAIN (ANALYZE, BUFFERS, VERBOSE)
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
);


QUERY PLAN


Delete on public.test t  (cost=235.37..278.69 rows=680 width=34) (actual time=0.049..0.051 rows=0 loops=1)

  Buffers: shared hit=2

  ->  Hash Join  (cost=235.37..278.69 rows=680 width=34) (actual time=0.049..0.050 rows=0 loops=1)

        Output: t.ctid, cte.*

        Inner Unique: true

        Hash Cond: (t.lpo_id = cte.lpo_id)

        Buffers: shared hit=2

        ->  Seq Scan on public.test t  (cost=0.00..30.40 rows=2040 width=10) (actual time=0.009..0.009 rows=1 loops=1)

              Output: t.ctid, t.lpo_id

              Buffers: shared hit=1

        ->  Hash  (cost=232.91..232.91 rows=197 width=32) (actual time=0.033..0.034 rows=0 loops=1)

              Output: cte.*, cte.lpo_id

              Buckets: 1024  Batches: 1  Memory Usage: 8kB

              Buffers: shared hit=1

              ->  HashAggregate  (cost=230.94..232.91 rows=197 width=32) (actual time=0.033..0.033 rows=0 loops=1)

                    Output: cte.*, cte.lpo_id

                    Group Key: cte.lpo_id

                    Buffers: shared hit=1

                    ->  Subquery Scan on cte  (cost=152.74..229.24 rows=680 width=32) (actual time=0.031..0.032 rows=0 loops=1)

                          Output: cte.*, cte.lpo_id

                          Filter: (cte.rn > 1)

                          Rows Removed by Filter: 17

                          Buffers: shared hit=1

                          ->  WindowAgg  (cost=152.74..203.74 rows=2040 width=20) (actual time=0.018..0.030 rows=17 loops=1)

                                Output: test.lpo_id, (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2)), row_number() OVER (?)

                                Buffers: shared hit=1

                                ->  Sort  (cost=152.74..157.84 rows=2040 width=12) (actual time=0.015..0.016 rows=17 loops=1)

                                      Output: (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2)), test.lpo_id

                                      Sort Key: (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2))

                                      Sort Method: quicksort  Memory: 25kB

                                      Buffers: shared hit=1

                                      ->  Seq Scan on public.test  (cost=0.00..40.60 rows=2040 width=12) (actual time=0.005..0.008 rows=17 loops=1)

                                            Output: LEAST(test.lang_1, test.lang_2), GREATEST(test.lang_1, test.lang_2), test.lpo_id

                                            Buffers: shared hit=1

Planning Time: 0.164 ms

Execution Time: 0.106 ms
lpo_id	lang_1	lang_2	least	greatest
1	6	4	4	6
2	4	6	4	6
3	6	68	6	68
4	68	6	6	68
5	18	6	6	18
6	6	18	6	18
7	36	18	18	36
8	18	36	18	36
9	1	2	1	2
10	1	3	1	3
11	1	4	1	4
12	1	5	1	5
13	1	6	1	6
14	1	7	1	7
15	1	8	1	8
16	2	3	2	3
17	2	4	2	4
18	2	5	2	5
19	2	6	2	6
20	2	7	2	7
21	2	8	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
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	lang_1	lang_2	least	greatest
1	6	4	4	6
2	4	6	4	6
3	6	68	6	68
4	68	6	6	68
5	18	6	6	18
6	6	18	6	18
7	36	18	18	36
8	18	36	18	36
9	1	2	1	2
10	1	3	1	3
11	1	4	1	4
12	1	5	1	5
13	1	6	1	6
14	1	7	1	7
15	1	8	1	8
16	2	3	2	3
17	2	4	2	4
18	2	5	2	5
19	2	6	2	6
20	2	7	2	7
21	2	8	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
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