Hi all,
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
*- But the query does not use the index... Why?*
Explain analyze:
> "Seq Scan on "inode_segments" (cost=0.00..3047212.44 rows=524846
> width=63) (actual time=14212.466..51428.439 rows=31 loops=1)"
> " Filter: ("split_part"(("full_path")::"text", '/'::"text", 4) =
> '12225'::"text")"
> " Rows Removed by Filter: 104361402"
> "Total runtime: 51428.482 ms"
Cheers
Lucas
