Performance regarding LIKE searches

От: randalls@bioinfo.wsu.edu
Тема: Performance regarding LIKE searches
Дата: ,
Msg-id: 2071262140.8761269880968618.JavaMail.root@mail
(см: обсуждение, исходный текст)
Ответы: Re: Performance regarding LIKE searches  (Tom Lane)
Re: Performance regarding LIKE searches  (Matthew Wakeling)
Список: 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, )

Hi,

I am querying a Postgresql 8.3 database table that has approximately 22 million records.  The (explain analyze) query
islisted 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
asn 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.032rows=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
andperforms like I would expect.  However, when my application, GBrowse, access the database, I see in my slow query
logthis: 

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-2822: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




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

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