Skip site navigation (1) Skip section navigation (2)

Re: Performance regarding LIKE searches

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: randalls(at)bioinfo(dot)wsu(dot)edu
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance regarding LIKE searches
Date: 2010-03-29 17:00:03
Message-ID: 16513.1269882003@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
randalls(at)bioinfo(dot)wsu(dot)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

In response to

Responses

pgsql-performance by date

Next:From: randallsDate: 2010-03-29 17:23:33
Subject: Re: Performance regarding LIKE searches
Previous:From: Greg SmithDate: 2010-03-29 16:53:33
Subject: Re: How much memory is PostgreSQL using

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group