Re: Performance regarding LIKE searches

Tom,

We are using perl 5.10 with postgresql DBD. Can you point me in the right direction in terms of unamed and named prepared statements?

Thanks,

Randall Svancara
Systems Administrator/DBA/Developer
Main Bioinformatics Laboratory

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: randalls(at)bioinfo(dot)wsu(dot)edu
Cc: pgsql-performance(at)postgresql(dot)org
Sent: Monday, March 29, 2010 10:00:03 AM
Subject: Re: [PERFORM] Performance regarding LIKE searches

randalls(at)bioinfo(dot)wsu(dot)edu writes:
> I can see I am hitting an index using an index that I created using the varchar_pattern_ops setting. This is very fast and performs like I would expect. However, when my application, GBrowse, access the database, I see in my slow query log this:

> 2010-03-29 09:34:38.083 PDT,"gdr_gbrowse_live","gdr_gbrowse_live",11649,"10.0.0.235:59043",4bb0399d.2d81,8,"SELECT",2010-03-28 22:24:45 PDT,4/118607,0,LOG,00000,"duration: 21467.467 ms execute dbdpg_p25965_9: SELECT f.id,f.object,f.typeid,f.seqid,f.start,f.end,f.strand
> 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