pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance regarding LIKE searches
5+ messages / 4 participants
[nested] [flat]
* Performance regarding LIKE searches
@ 2010-03-29 16:42 randalls@bioinfo.wsu.edu
2010-03-29 17:00 ` Re: Performance regarding LIKE searches Tom Lane <tgl@sss.pgh.pa.us>
2010-03-30 09:34 ` Re: Performance regarding LIKE searches Matthew Wakeling <matthew@flymine.org>
0 siblings, 2 replies; 5+ messages in thread
From: randalls@bioinfo.wsu.edu @ 2010-03-29 16:42 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am querying a Postgresql 8.3 database table that has approximately 22 million records. The (explain analyze) query is listed below:
gdr_gbrowse_live=> explain analyze 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 'Scaffold:scaffold_163:1000..1199%' AND n.display_name>0);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.01..5899.93 rows=734 width=884) (actual time=0.033..0.033 rows=0 loops=1)
-> Index Scan using name_name_lower_pattern_ops_idx on name n (cost=0.01..9.53 rows=734 width=4) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: ((lower((name)::text) ~>=~ 'Scaffold:scaffold'::text) AND (lower((name)::text) ~<~ 'Scaffold:scaffole'::text))
Filter: ((display_name > 0) AND (lower((name)::text) ~~ 'Scaffold:scaffold_163:1000..1199%'::text))
-> Index Scan using feature_pkey on feature f (cost=0.00..8.01 rows=1 width=884) (never executed)
Index Cond: (f.id = n.id)
Total runtime: 0.119 ms
(7 rows)
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?
Randall Svancara
Systems Administrator/DBA/Developer
Main Bioinformatics Laboratory
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance regarding LIKE searches
2010-03-29 16:42 Performance regarding LIKE searches randalls@bioinfo.wsu.edu
@ 2010-03-29 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-29 17:23 ` Re: Performance regarding LIKE searches randalls@bioinfo.wsu.edu
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-03-29 17:00 UTC (permalink / raw)
To: randalls@bioinfo.wsu.edu; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance regarding LIKE searches
2010-03-29 16:42 Performance regarding LIKE searches randalls@bioinfo.wsu.edu
2010-03-29 17:00 ` Re: Performance regarding LIKE searches Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-03-29 17:23 ` randalls@bioinfo.wsu.edu
2010-03-29 17:36 ` Re: Performance regarding LIKE searches Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 5+ messages in thread
From: randalls@bioinfo.wsu.edu @ 2010-03-29 17:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance regarding LIKE searches
2010-03-29 16:42 Performance regarding LIKE searches randalls@bioinfo.wsu.edu
2010-03-29 17:00 ` Re: Performance regarding LIKE searches Tom Lane <tgl@sss.pgh.pa.us>
2010-03-29 17:23 ` Re: Performance regarding LIKE searches randalls@bioinfo.wsu.edu
@ 2010-03-29 17:36 ` Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 5+ messages in thread
From: Andy Colson @ 2010-03-29 17:36 UTC (permalink / raw)
To: randalls@bioinfo.wsu.edu; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance regarding LIKE searches
2010-03-29 16:42 Performance regarding LIKE searches randalls@bioinfo.wsu.edu
@ 2010-03-30 09:34 ` Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 5+ messages in thread
From: Matthew Wakeling @ 2010-03-30 09:34 UTC (permalink / raw)
To: randalls@bioinfo.wsu.edu; +Cc: pgsql-performance
On Mon, 29 Mar 2010, randalls@bioinfo.wsu.edu wrote:
> WHERE ... lower(n.name) LIKE 'Scaffold:scaffold_163:1000..1199%' ...
I'm sure you noticed that this is never going to return any rows?
Matthew
--
Me... a skeptic? I trust you have proof?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-03-30 09:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-29 16:42 Performance regarding LIKE searches randalls@bioinfo.wsu.edu
2010-03-29 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-29 17:23 ` randalls@bioinfo.wsu.edu
2010-03-29 17:36 ` Andy Colson <andy@squeakycode.net>
2010-03-30 09:34 ` Matthew Wakeling <matthew@flymine.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox