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.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE test
(
  id INT PRIMARY KEY,
  t_field TEXT
);
INSERT INTO test 
VALUES
(1, 'bar$foo$john$doe$xxx');
1 rows affected

SELECT 
  REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-')) AS result
FROM
  test;


result


bar$foo$john$doe-xxx


SELECT
  UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))
FROM
  test;


unnest


xxx

eod

nhoj

oof

rab


SELECT
  UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$')) AS str
FROM
  test;


str


xxx

eod

nhoj

oof

rab


SELECT
  t.id, x.elem, x.num
FROM test t
LEFT JOIN LATERAL
  UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))
  WITH ORDINALITY AS x (elem, num) ON TRUE;


id
elem
num


1
xxx
1

1
eod
2

1
nhoj
3

1
oof
4

1
rab
5


SELECT
  (STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test)), '$'))
FROM test;


string_to_array


{xxx,eod,nhoj,oof,rab}


SELECT 
  REVERSE(
  (STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test)), '$'))[1] 
  || '-' ||
  STRING_AGG(elem, '$' ORDER BY num))
FROM
(
  SELECT
    t.id, x.elem, x.num
  FROM test t
  LEFT JOIN LATERAL
    UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))
    WITH ORDINALITY AS x (elem, num) ON TRUE
) AS t
WHERE num > 1;


reverse


bar$foo$john$doe-xxx


EXPLAIN (ANALYZE, TIMING OFF)
SELECT 
  GENERATE_SERIES(1, 1000000),
  REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-')) AS result
FROM
  test;


QUERY PLAN


ProjectSet  (cost=0.00..6350041.75 rows=1270000000 width=36) (actual rows=1000000 loops=1)

  ->  Seq Scan on test  (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)

Planning Time: 0.031 ms

Execution Time: 1145.687 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
  REVERSE(
  (STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test)), '$'))[1] 
  || '-' ||
  STRING_AGG(elem, '$' ORDER BY num))
FROM
(
  SELECT
    t.id, x.elem, x.num
  FROM test t
  LEFT JOIN LATERAL
    UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))
    WITH ORDINALITY AS x (elem, num) ON TRUE
) AS t
WHERE num > 1;


QUERY PLAN


ProjectSet  (cost=251.78..5251.81 rows=1000000 width=36) (actual rows=1000000 loops=1)

  InitPlan 1 (returns $0)

    ->  Seq Scan on test  (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)

  ->  Aggregate  (cost=229.08..229.09 rows=1 width=32) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.01..219.56 rows=3810 width=40) (actual rows=4 loops=1)

              ->  Seq Scan on test t  (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1)

              ->  Function Scan on unnest x  (cost=0.01..0.13 rows=3 width=40) (actual rows=4 loops=1)

                    Filter: (num > 1)

                    Rows Removed by Filter: 1

Planning Time: 0.176 ms

Execution Time: 1031.471 ms