Re: Performance regarding LIKE searches

От: Andy Colson
Тема: Re: Performance regarding LIKE searches
Дата: ,
(см: обсуждение, исходный текст)
Ответ на: Re: Performance regarding LIKE searches  ()
Список: pgsql-performance

Скрыть дерево обсуждения

Performance regarding LIKE searches  (, )
 Re: Performance regarding LIKE searches  (Tom Lane, )
  Re: Performance regarding LIKE searches  (, )
   Re: Performance regarding LIKE searches  (Andy Colson, )
 Re: Performance regarding LIKE searches  (Matthew Wakeling, )

On 3/29/2010 12:23 PM,  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
> Thanks,
> Randall Svancara
> Systems Administrator/DBA/Developer
> Main Bioinformatics Laboratory
> ----- Original Message -----
> From: "Tom Lane"<>
> To: 
> Cc: 
> Sent: Monday, March 29, 2010 10:00:03 AM
> Subject: Re: [PERFORM] Performance regarding LIKE searches
>  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,4/118607,0,LOG,00000,"duration:21467.467 ms  execute dbdpg_p25965_9: SELECT,f.object,f.typeid,f.seqid,f.start,f.end,f.strand
>>    FROM feature as f, name as n
>>    WHERE ( AND lower( 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

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.


В списке pgsql-performance по дате сообщения:

От: Faheem Mitha
Сообщение: Re: experiments in query optimization
От: Josh Berkus
Сообщение: Re: why does swap not recover?