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


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
  REVERSE(
  (STRING_TO_ARRAY(REVERSE((SELECT 'bar$foo$john$doe$xxx')), '$'))[1] 
  || '-' ||
  STRING_AGG(elem, '$' ORDER BY num))
FROM
(
  SELECT
    x.elem, x.num
  FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS t(t_field)
  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=0.08..5000.11 rows=1000000 width=36) (actual rows=1000000 loops=1)

  InitPlan 1 (returns $0)

    ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual rows=1 loops=1)

  ->  Aggregate  (cost=0.07..0.08 rows=1 width=32) (actual rows=1 loops=1)

        ->  Function Scan on unnest x  (cost=0.00..0.07 rows=2 width=40) (actual rows=4 loops=1)

              Filter: (num > 1)

              Rows Removed by Filter: 1

Planning Time: 0.334 ms

Execution Time: 1027.829 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT 
  GENERATE_SERIES(1, 1000000),
  REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-')) AS result
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS test(t_field);


QUERY PLAN


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

  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)

Planning Time: 0.113 ms

Execution Time: 56.763 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
 GENERATE_SERIES(1, 1000000),
 REVERSE(
 SUBSTRING(REVERSE(t_field) FROM 1 FOR POSITION('$' IN REVERSE(t_field)) - 1)
 || '-' ||
 SUBSTRING(REVERSE(t_field) FROM POSITION('$' IN REVERSE(t_field)) + 1 FOR (LENGTH(REVERSE(t_field)))))
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS test(t_field);


QUERY PLAN


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

  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)

Planning Time: 0.051 ms

Execution Time: 56.646 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
  REVERSE(
  SUBSTRING 
  (REVERSE(t_field) FROM 1 FOR (LENGTH((STRING_TO_ARRAY(REVERSE(t_field), '$'))[1])))
  || '-'
  ||
  SUBSTRING
  (REVERSE(t_field) FROM 5 FOR (LENGTH(REVERSE(t_field)))))
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS test(t_field);


QUERY PLAN


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

  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)

Planning Time: 0.050 ms

Execution Time: 56.642 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
regexp_replace(t_field, '(.*)\$', '\1-' )
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS test(t_field);


QUERY PLAN


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

  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)

Planning Time: 0.124 ms

Execution Time: 56.749 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
    OVERLAY(t_field placing '-' 
       from length(t_field) + 1 - STRPOS(REVERSE(t_field), '$')
         ) AS result
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS test(t_field);


QUERY PLAN


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

  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)

Planning Time: 0.039 ms

Execution Time: 56.442 ms