Postgres 14

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 14.1 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),
  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.594 ms

Execution Time: 1021.041 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.063 ms

Execution Time: 1131.326 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.069 ms

Execution Time: 1461.230 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.071 ms

Execution Time: 1804.348 ms


-- ypercube
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.066 ms

Execution Time: 649.266 ms