Re: Performance regarding LIKE searches - Mailing list pgsql-performance

From randalls@bioinfo.wsu.edu
Subject Re: Performance regarding LIKE searches
Date
Msg-id 2002514254.8941269883413114.JavaMail.root@mail
Whole thread Raw
In response to Re: Performance regarding LIKE searches  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Performance regarding LIKE searches  (Andy Colson <andy@squeakycode.net>)
List pgsql-performance
Tom,

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

Thanks,

Randall Svancara
Systems Administrator/DBA/Developer
Main Bioinformatics Laboratory



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

randalls@bioinfo.wsu.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
fastand performs like I would expect.  However, when my application, GBrowse, access the database, I see in my slow
querylog 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-2822: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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Performance regarding LIKE searches
Next
From: Andy Colson
Date:
Subject: Re: Performance regarding LIKE searches