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@xxxxxxxxxxxxx> To: randalls@xxxxxxxxxxxxxxx Cc: pgsql-performance@xxxxxxxxxxxxxx Sent: Monday, March 29, 2010 10:00:03 AM Subject: Re: Performance regarding LIKE searches randalls@xxxxxxxxxxxxxxx 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
