Re: Query tuning help

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