On Sunday, May 1, 2016,
drum.lucas@gmail.com <
drum.lucas@gmail.com> wrote:
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?
The most obvious reason is that the index is partial but the query doesn't contain an appropriate where clause.
I'm also not sure how well the planner can move around the functional expression in the select-list so that it matches up in the where clause to then match the index.
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"
These stats seem wacky...and seem to be missing stuff like the inodes table...
David J.