I'm not sure about this which is why I'm replying off list, but your index is on file_type, file_parent_dir, and file_name and you're query is on file_parent_dir and file_name.
I seem to remember reading that that the index will only get used if the columns in the where clause "match up" "in order".
That is um... if you have an index on columns a and b and a where clause of "b = 1" it woin't use the index since the index "looks like"
a, b a, b a, b etc...
Does that make any sense? Not sure if that's right or not, but easy enough to remove the "file_type" from your index and try it.
post back to the list if that's it.
-philip
Thanks for the reply!
I have played around a little more and have created a few different test Indexes and it looks like it is the regex that is causing it to do the sequential scan. If I remove the regex and create a 'file_parent_dir', 'file_name' index it will use it. If I create an Index just for 'file_parent_dir' and change my UPDATE to just look for the regex '... WHERE file_parent_dir~'^/<dir>'...' it will still do the sequential scan anyway.
So I need to either find an Index that will work with regexes or re-write my code to update each subdirectory separately and use simpler UPDATE statement for each.
Thanks again!
Madison
PS - I cc'ed the list to follow up on what I found out so far. (Hi list!)
-- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
