Postgres 13

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


CREATE TABLE current (current_data TEXT NOT NULL);
CREATE TABLE expected_output 
( 
  shared INT NOT NULL, 
  expected_data TEXT NOT NULL
);
INSERT INTO current (current_data) VALUES 
('992,1005,1007,1008'),
('44,1005,1110'),
('13,44,1005,10078'),
('11,1203,6666'),
('1,11,99,2222'),
('1234');           -- note the singleton!
6 rows affected

SELECT 
  c.current_data AS d,
  ROW_NUMBER() OVER () AS rn
FROM current c;


d
rn


992,1005,1007,1008
1

44,1005,1110
2

13,44,1005,10078
3

11,1203,6666
4

1,11,99,2222
5

1234
6


SELECT 
  UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d,
  ROW_NUMBER() OVER () AS rn
FROM current c
ORDER BY rn, d;


d
rn


992
1

1005
1

1007
1

1008
1

44
2

1005
2

1110
2

13
3

44
3

1005
3

10078
3

11
4

1203
4

6666
4

1
5

11
5

99
5

2222
5

1234
6


SELECT d, COUNT(d) FROM
(
  SELECT 
    REGEXP_SPLIT_TO_TABLE(c.current_data, ',')::INT AS d,
    ROW_NUMBER() OVER () AS rn  
  FROM current c
) AS t01
GROUP BY d
HAVING COUNT(d) > 1;


d
count


11
2

44
2

1005
3


SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM
(
  SELECT d, COUNT(d) AS cnt
  FROM
  (
    SELECT 
      UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d,
      ROW_NUMBER() OVER () AS rn
    FROM current c
  ) AS t01
  GROUP BY d
  HAVING COUNT(d) > 1
) AS t02
JOIN
(
  SELECT 
    STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2
  FROM current c
) AS t03
ON t02.d = ANY(t03.d2);


num
val


44
1005

44
1110

11
99

11
2222

1005
992

1005
1008

11
11

44
10078

11
6666

11
1

1005
44

44
13

1005
1007

1005
1110

1005
13

11
1203

44
44

1005
10078

1005
1005


SELECT 
  num AS "Token", -- the token is not required and can be omitted
  STRING_AGG(val::TEXT, ',' ORDER BY val::INT) AS "The string"
FROM
(
  SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM
  (
    SELECT d, COUNT(d) AS cnt
    FROM
    (
      SELECT 
        UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d,
        ROW_NUMBER() OVER () AS rn
      FROM current c
    ) AS t01
    GROUP BY d
    HAVING COUNT(d) > 1
  ) AS t02
  JOIN
  (
    SELECT 
      STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2
    FROM current c
  ) AS t03
  ON t02.d = ANY(t03.d2)
) AS t04
GROUP BY num
ORDER BY num;


Token
The string


11
1,11,99,1203,2222,6666

44
13,44,1005,1110,10078

1005
13,44,992,1005,1007,1008,1110,10078


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT num AS "Token", STRING_AGG(val::TEXT, ',' ORDER BY val::INT) AS "The string"
FROM
(
  SELECT DISTINCT t02.d AS num, UNNEST(t03.d2) AS val FROM
  (
    SELECT d, COUNT(d) AS cnt
    FROM
    (
      SELECT 
        UNNEST(STRING_TO_ARRAY(c.current_data, ','))::INT AS d,
        ROW_NUMBER() OVER () AS rn
      FROM current c
    ) AS t01
    GROUP BY d
    HAVING COUNT(d) > 1
  ) AS t02
  JOIN
  (
    SELECT 
      STRING_TO_ARRAY(c.current_data, ',')::INT[] AS d2
    FROM current c
  ) AS t03
  ON t02.d = ANY(t03.d2)
) AS t04
GROUP BY num
ORDER BY 1;


QUERY PLAN


GroupAggregate  (cost=12077.82..13305.45 rows=200 width=36) (actual time=0.112..0.127 rows=3 loops=1)

  Output: t02.d, string_agg(((unnest((string_to_array(c.current_data, ','::text))::integer[])))::text, ','::text ORDER BY (unnest((string_to_array(c.current_data, ','::text))::integer[])))

  Group Key: t02.d

  Buffers: shared hit=2

  ->  Unique  (cost=12077.82..12411.95 rows=44550 width=8) (actual time=0.094..0.102 rows=19 loops=1)

        Output: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))

        Buffers: shared hit=2

        ->  Sort  (cost=12077.82..12189.20 rows=44550 width=8) (actual time=0.094..0.097 rows=25 loops=1)

              Output: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))

              Sort Key: t02.d, (unnest((string_to_array(c.current_data, ','::text))::integer[]))

              Sort Method: quicksort  Memory: 26kB

              Buffers: shared hit=2

              ->  ProjectSet  (cost=581.20..8637.86 rows=44550 width=8) (actual time=0.056..0.084 rows=25 loops=1)

                    Output: t02.d, unnest((string_to_array(c.current_data, ','::text))::integer[])

                    Buffers: shared hit=2

                    ->  Nested Loop  (cost=581.20..8125.54 rows=4455 width=36) (actual time=0.054..0.073 rows=7 loops=1)

                          Output: c.current_data, t02.d

                          Join Filter: (t02.d = ANY ((string_to_array(c.current_data, ','::text))::integer[]))

                          Rows Removed by Join Filter: 11

                          Buffers: shared hit=2

                          ->  Seq Scan on public.current c  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.007..0.008 rows=6 loops=1)

                                Output: c.current_data

                                Buffers: shared hit=1

                          ->  Materialize  (cost=581.20..584.71 rows=67 width=4) (actual time=0.006..0.007 rows=3 loops=6)

                                Output: t02.d

                                Buffers: shared hit=1

                                ->  Subquery Scan on t02  (cost=581.20..584.37 rows=67 width=4) (actual time=0.031..0.035 rows=3 loops=1)

                                      Output: t02.d

                                      Buffers: shared hit=1

                                      ->  HashAggregate  (cost=581.20..583.70 rows=67 width=12) (actual time=0.031..0.034 rows=3 loops=1)

                                            Output: (((unnest(string_to_array(c_1.current_data, ','::text))))::integer), NULL::bigint

                                            Group Key: ((unnest(string_to_array(c_1.current_data, ','::text))))::integer

                                            Filter: (count((((unnest(string_to_array(c_1.current_data, ','::text))))::integer)) > 1)

                                            Batches: 1  Memory Usage: 40kB

                                            Rows Removed by Filter: 12

                                            Buffers: shared hit=1

                                            ->  Result  (cost=0.00..377.20 rows=13600 width=12) (actual time=0.010..0.022 rows=19 loops=1)

                                                  Output: ((unnest(string_to_array(c_1.current_data, ','::text))))::integer, NULL::bigint

                                                  Buffers: shared hit=1

                                                  ->  ProjectSet  (cost=0.00..105.20 rows=13600 width=32) (actual time=0.009..0.018 rows=19 loops=1)

                                                        Output: unnest(string_to_array(c_1.current_data, ','::text))

                                                        Buffers: shared hit=1

                                                        ->  Seq Scan on public.current c_1  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.005 rows=6 loops=1)

                                                              Output: c_1.current_data

                                                              Buffers: shared hit=1

Planning Time: 0.192 ms

Execution Time: 0.206 ms
d	rn
992,1005,1007,1008	1
44,1005,1110	2
13,44,1005,10078	3
11,1203,6666	4
1,11,99,2222	5
1234	6
d	rn
992	1
1005	1
1007	1
1008	1
44	2
1005	2
1110	2
13	3
44	3
1005	3
10078	3
11	4
1203	4
6666	4
1	5
11	5
99	5
2222	5
1234	6
Token	The string
11	1,11,99,1203,2222,6666
44	13,44,1005,1110,10078
1005	13,44,992,1005,1007,1008,1110,10078