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

CREATE TABLE with_filename
(
  file_id INTEGER,
  file_name VARCHAR (256)
);

INSERT INTO with_filename
VALUES
(1, '/users/mcm1/ualaoip2/vmm/file1.pdf'),
(2, '/users/mcm1/ualaoip2/vmm/file2.py'),
(3, '/users/mcm1/ualaoip2/vmm/file3.pdf'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');
5 rows affected

-- SELECT * FROM with_filename;
SELECT regexp_split_to_table(w.file_name, '^/.*/.*/......../.../') AS fn
FROM with_filename w

fn

file1.pdf

file2.py

file3.pdf

file8.tar.gz

/users/mcm1/my_prog.cpp

SELECT regexp_split_to_table(w.file_name, '^(/.*/)') AS fn
FROM with_filename w

fn

file1.pdf

file2.py

file3.pdf

file8.tar.gz

my_prog.cpp

SELECT regexp_split_to_table(w.file_name, '^(/.*/).*(\.^)' AS fn
FROM with_filename w
ERROR:  syntax error at or near "AS"
LINE 1: ...CT regexp_split_to_table(w.file_name, '^(/.*/).*(\.^)' AS fn
                                                                  ^

SELECT regexp_split_to_table(w.file_name, '\/$') AS fn
FROM with_filename w

fn

/users/mcm1/ualaoip2/vmm/file1.pdf

/users/mcm1/ualaoip2/vmm/file2.py

/users/mcm1/ualaoip2/vmm/file3.pdf

/users/mcm1/ualaoip2/vmm/file8.tar.gz

/users/mcm1/my_prog.cpp

SELECT regexp_split_to_table(w.file_name, E'\/[a-z]*$') AS fn
FROM with_filename w

fn

/users/mcm1/ualaoip2/vmm/file1.pdf

/users/mcm1/ualaoip2/vmm/file2.py

/users/mcm1/ualaoip2/vmm/file3.pdf

/users/mcm1/ualaoip2/vmm/file8.tar.gz

/users/mcm1/my_prog.cpp