Re: index question

On Sunday, May 1, 2016, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.