On 3/29/2010 12:23 PM, randa...@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"<t...@sss.pgh.pa.us>
To: randa...@bioinfo.wsu.edu
Cc: pgsql-performance@postgresql.org
Sent: Monday, March 29, 2010 10:00:03 AM
Subject: Re: [PERFORM] Performance regarding LIKE searches
randa...@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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
