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.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


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'),
(4, '/users/mcm1/ualaoip2/vmm/file4.c'),
(5, '/users/mcm1/ualaoip2/vmm/file5.java'),
(6, '/users/mcm1/ualaoip2/vmm/file6.class'),
(7, '/users/mcm1/ualaoip2/vmm/file7'),
(8, '/users/mcm1/ualaoip2/vmm/file8.tar.gz'),
(9, '/users/mcm1/my_prog.cpp');

CREATE TABLE



INSERT 0 9


SELECT
    file_name,
    REGEXP_REPLACE(file_name, '^.*/([^/]*?)(\.[^/.]+)?$', '\1') AS filename
FROM with_filename;


file_name
filename


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

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

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

/users/mcm1/ualaoip2/vmm/file4.c
file4

/users/mcm1/ualaoip2/vmm/file5.java
file5

/users/mcm1/ualaoip2/vmm/file6.class
file6

/users/mcm1/ualaoip2/vmm/file7
file7

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

/users/mcm1/my_prog.cpp
my_prog



SELECT 9

file_name	filename
/users/mcm1/ualaoip2/vmm/file1.pdf	file1
/users/mcm1/ualaoip2/vmm/file2.py	file2
/users/mcm1/ualaoip2/vmm/file3.pdf	file3
/users/mcm1/ualaoip2/vmm/file4.c	file4
/users/mcm1/ualaoip2/vmm/file5.java	file5
/users/mcm1/ualaoip2/vmm/file6.class	file6
/users/mcm1/ualaoip2/vmm/file7	file7
/users/mcm1/ualaoip2/vmm/file8.tar.gz	file8.tar
/users/mcm1/my_prog.cpp	my_prog