Message-ID: <4BB0E504.30300@squeakycode.net>
Date: Mon, 29 Mar 2010 12:36:04 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3
MIME-Version: 1.0
To: randalls@bioinfo.wsu.edu, pgsql-performance@postgresql.org
Subject: Re: Performance regarding LIKE searches
References: <16513.1269882003@sss.pgh.pa.us>
	<2002514254.8941269883413114.JavaMail.root@mail>
In-Reply-To: <2002514254.8941269883413114.JavaMail.root@mail>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 3/29/2010 12:23 PM, randalls@bioinfo.wsu.edu wrote:
> 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@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 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
>

I'm just going to guess, but DBD::Pg can do "real prepare" or "fake 
prepare".

It does "real" by default.  Try setting:
$dbh->{pg_server_prepare} = 0;

before you prepare/run that statement and see if it makes a difference.

http://search.cpan.org/dist/DBD-Pg/Pg.pm#prepare


-Andy