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 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  t_field TEXT
);
INSERT INTO test (t_field)
VALUES ('bar$foo$john$doe$xxx');  -- OP's own data
1 rows affected

INSERT INTO test (t_field)
SELECT 
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
  LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT)
FROM 
  GENERATE_SERIES(1, 299999);  --<<== Vary here
   
  --
  -- Vary the GENERATE_SERIES(1, 299999) number if the fiddle run fails!
  --
  -- 299999 (+ 1 - OP) = 300,000 = large enough number to get reliable, consistent results
  -- and small enough so that the fiddle run doesn't fail too often.
  -- 
299999 rows affected

ANALYZE test;
--
-- 4: Another "pure" string function approach - very much less efficient than 
-- the fastest
--

SELECT
  t_field,
  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
LIMIT 2;


t_field
reverse


bar$foo$john$doe$xxx
bar$foo$john$doe-xxx

f13$b93$70ba$ad63$9aefb
f13$b93$70ba$ad63-9aefb


EXPLAIN(ANALYZE, TIMING OFF)
SELECT
  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


Seq Scan on test  (cost=0.00..14218.00 rows=300000 width=32) (actual rows=300000 loops=1)

Planning Time: 0.031 ms

Execution Time: 659.039 ms

t_field	reverse
bar$foo$john$doe$xxx	bar$foo$john$doe-xxx
f13$b93$70ba$ad63$9aefb	f13$b93$70ba$ad63-9aefb