On Sun, May 1, 2016 at 7:27 PM, drum.lu...@gmail.com <drum.lu...@gmail.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.
