Message-ID: <4281FEE3.6080506@relevanttraffic.se>
Date: Wed, 11 May 2005 14:47:31 +0200
From: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
Organization: Relevant Traffic AB
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
In-Reply-To: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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