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