> I can see I am hitting an index using an index that I created using the varchar_pattern_ops setting. This is very
fastand performs like I would expect. However, when my application, GBrowse, access the database, I see in my slow
> 2010-03-29 09:34:38.083
PDT,4/118607,0,LOG,00000,"duration:21467.467 ms execute dbdpg_p25965_9: SELECT
> FROM feature as f, name as n
> WHERE (n.id=f.id AND lower(n.name) LIKE $1)
> ","parameters: $1 = 'Scaffold:scaffold\_163:1000..1199%'",,,,,,,
> GBrowse is a perl based application. Looking at the duration for this query is around 21 seconds. That is a bit
long. Does anyone have any ideas why the query duration is so different?
You're not going to get an index optimization when the LIKE pattern
isn't a constant (and left-anchored, but this is).
It is possible to get the planner to treat a query parameter as a
constant (implying a re-plan on each execution instead of having a
cached plan). I believe what you have to do at the moment is use
unnamed rather than named prepared statements. The practicality of
this would depend a lot on your client-side software stack, which
you didn't mention.
regards, tom lane