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

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 test ;


QUERY PLAN


ProjectSet  (cost=0.00..6350051.28 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.079 ms

Execution Time: 948.344 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((SELECT t_field FROM test)), '$'))
    WITH ORDINALITY AS x (elem, num) ON TRUE
) AS t
WHERE num > 1;


QUERY PLAN


ProjectSet  (cost=128.56..5128.59 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)

  InitPlan 2 (returns $1)

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

  ->  Aggregate  (cost=83.16..83.17 rows=1 width=32) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.01..73.63 rows=3810 width=40) (actual rows=4 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

              ->  Materialize  (cost=0.00..29.05 rows=1270 width=0) (actual rows=1 loops=4)

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

Planning Time: 0.604 ms

Execution Time: 1302.000 ms


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.078 ms

Execution Time: 1434.311 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 test;


QUERY PLAN


ProjectSet  (cost=0.00..6350079.85 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.093 ms

Execution Time: 1999.928 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))))) AS result
FROM test;


QUERY PLAN


ProjectSet  (cost=0.00..6350070.33 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.086 ms

Execution Time: 2255.108 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 test;


QUERY PLAN


ProjectSet  (cost=0.00..6350070.33 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.086 ms

Execution Time: 2281.977 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
regexp_replace(t_field, '(.*)\$', '\1-' )
FROM test;


QUERY PLAN


ProjectSet  (cost=0.00..6350035.40 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.073 ms

Execution Time: 3400.580 ms