Postgres 10

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?.

CREATE TABLE data
    ("file_name" varchar(17))
;
    
INSERT INTO data
    ("file_name")
VALUES
    ('foo/bar/file1.txt'),
    ('foo/bar/file2.txt'),
    ('file3.txt'),
    ('foo/bar/file4.txt'),
    ('file5.txt'),
    ('file6.txt')
;
6 rows affected

UPDATE data SET file_name =  SUBSTRING(file_name from 'foo/bar/(.*)$')
where file_name LIKE 'foo/bar%';
3 rows affected

select * from data;


file_name


file3.txt

file5.txt

file6.txt

file1.txt

file2.txt

file4.txt