От: Ulrich Wisser
Тема: Re: Query tuning help
Дата: ,
Msg-id: 4281FEE3.6080506@relevanttraffic.se
(см: обсуждение, исходный текст)
Ответ на: Query tuning help  (Dan Harris)
Список: pgsql-performance

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

Query tuning help  (Dan Harris, )
 Re: Query tuning help  (Josh Berkus, )
 Re: Query tuning help  (Russell Smith, )
  Re: Query tuning help  (Tom Lane, )
  Re: Query tuning help  (Dan Harris, )
   Re: Query tuning help  (Josh Berkus, )
    Re: Query tuning help  (Dan Harris, )
     Re: Query tuning help  (Klint Gore, )
   Re: Query tuning help  (Tom Lane, )
   Re: Query tuning help  (Russell Smith, )
    Re: Query tuning help  (Dan Harris, )
    Re: Query tuning help  (Mischa Sandberg, )
 Re: Query tuning help  (Harald Fuchs, )
 Re: Query tuning help  (Ulrich Wisser, )

Hi Dan,

I tried to understand your query, but I couldn't get my understanding of
the query and your description in sync.

Why do you use sub selects? Wouldn't a simple "recordtext like '%RED%'"
do the trick too?

You combine all your where conditions with and. To me this looks like
you get only rows with RED and CORVETTE.

 From your description I would rewrite the query as

explain analyze
select distinct
     em.incidentid,
     ea.recordtext as retdata,
     eg.long,
     eg.lat
from
     ea join em using(incidentid) join eg using(incidentid)
where
         em.entrydate >= '2005-1-1 00:00'::date
     and em.entrydate <= '2005-5-9 00:00'::date
     and (  recordtext like '%RED%' or recordtext like '%CORVETTE%'  )
order by em.entrydate


That should give you all rows containing one of the words.
Does it work?
Is is faster? Is it fast enough?

Ulrich


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

От: Josh Berkus
Дата:
Сообщение: Re: Bad plan after vacuum analyze
От: Tom Lane
Дата:
Сообщение: Re: Bad plan after vacuum analyze