I've got the following index on the gorfs.inode_segments table:
CREATE INDEX ix_clientids ON gorfs.inode_segments USING btree (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
And I'm running the following Query:
SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT AS variation, st_size, segment_index, reverse(split_part(reverse(full_path), '/', 1)) as file_name, i.st_ino, full_path FROM gorfs.inodes i JOIN gorfs.inode_segments s ON i.st_ino = s.st_ino_target WHERE i.checksum_md5 IS NOT NULL AND s.full_path ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' AND i.st_size > 0) as test WHERE account_id = 12225
