Re: index question

On Sun, May 1, 2016 at 5:40 PM, 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?*
>
> 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
>
>
Well, a little more information would be useful like:
1. What is the PostgreSQL version?
2. What is the O/S?
3. What is the structure of gorfs.inode_segments?
4. Did you do an ANALYZE table gorfs.inode_segments after you created the
index?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.