Re: index question

On Sun, May 1, 2016 at 7:27 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

​Repeating the query to improve the self-containment aspect of the email
would have been appreciated.
​

>
> if possible please have a look on the explain analyze results:
>
> http://explain.depesz.com/s/rHOU
>
> What else can I do?
>
> *The indexes I created is:*
> - CREATE INDEX CONCURRENTLY ix_inode_segments_notes_clientids2 ON
> gorfs.inode_segments USING btree ("full_path");
>
>
​the only condition that could even potentially use this index​ is:

s.full_path ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'

My knowledge is limited in this area, and the documentation covers this
specific dynamic only minimally, but for certain attempting to perform an
un-anchored regexp match using a btree index is impossible.

These leaves to avenues to explore.

1) See if a start-of-string anchor will make the btree index usable
2) Use the pg_trgm contrib module

http://www.postgresql.org/docs/current/static/pgtrgm.html

- CREATE INDEX CONCURRENTLY ix_inodes_checksum_st_size ON gorfs.inodes
> USING btree ("checksum_md5","st_size");
>
​
This one was used.​

​IMO you are leaving too much infomation encoded in the full_path. I'd
personally setup triggers to parse out the components on insert/update into
fields and then index those fields. In fact I'd probably use some form of
inheritance or other one-to-one relationship here.

David J.
​