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 test1 (
id serial,
path character varying(255),
client_id bigint,
PRIMARY KEY (id)
);
INSERT INTO test1(client_id,path) VALUES (1,'/testfile/'); -- root path
INSERT INTO test1(client_id,path) VALUES (1,'/testfile/client/'); -- root path
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/1000/');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/1000/master/');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/1000/master/2000');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/1000/master/2000/master');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/1000/small/');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/unassigned/file/1001/master');
INSERT INTO test1(client_id,path) VALUES (10,'/testfile/client/10/attachment/unassigned/file/1002/master');
SELECT * FROM test1 WHERE client_id = 10 AND path ~ '^/testfile/client/[0-9]+/attachment/(([0-9]{1,14})|(unassigned))/';
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
| id | path | client_id |
|---|---|---|
| 4 | /testfile/client/10/attachment/1000/ | 10 |
| 5 | /testfile/client/10/attachment/1000/master/ | 10 |
| 6 | /testfile/client/10/attachment/1000/master/2000 | 10 |
| 7 | /testfile/client/10/attachment/1000/master/2000/master | 10 |
| 8 | /testfile/client/10/attachment/1000/small/ | 10 |
| 9 | /testfile/client/10/attachment/unassigned/file/1001/master | 10 |
| 10 | /testfile/client/10/attachment/unassigned/file/1002/master | 10 |