Message-ID: <1115659918.427f9e8ee7789@webmail.telus.net>
Date: Mon,  9 May 2005 10:31:58 -0700
From: Mischa Sandberg <mischa.sandberg@telus.net>
To: Russell Smith <mr-russ@pws.com.au>
Cc: Dan Harris <fbsd@drivefaster.net>,
	pgsql-performance@postgresql.org
Subject: Re: Query tuning help
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
	<200505091051.14689.mr-russ@pws.com.au>
	<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
	<200505091232.05495.mr-russ@pws.com.au>
In-Reply-To: <200505091232.05495.mr-russ@pws.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs

Quoting Russell Smith <mr-russ@pws.com.au>:

> On Mon, 9 May 2005 11:49 am, Dan Harris wrote:
> > On May 8, 2005, at 6:51 PM, Russell Smith wrote:
> [snip]
> > select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat
> > FROM em
> > JOIN ea ON em.incidentid = ea.incidentid  --- slight paraphrase /Mischa.
> >         AND em.entrydate between '2005-1-1' and '2005-5-9'
> >         AND ea.recordtext like '%RED%'  AND ea.recordtext like
'%CORVETTE%'

> > Here's the situation:
> > Due to the format of the systems with which I integrate ( I have no
> > control over these formats ), we will get these 'recordtext' values one
> > line at a time, accumulating over time.  The only way I can find to
> > make this work is to insert a new record for each line.  The problem
> > is, that when someone wants to search multiple keywords, they expect
> > these words to be matched across multiple records with a given incident
> > number.
> >
> >   For a very simple example:
> >
> > IncidentID  Date    Recordtext
> > --------------  -------------
> > 11111   2005-05-01 14:21 blah blah blah RED blah blah
> > 2222   2005-05-01 14:23 not what we are looking for
> > 11111   2005-05-02 02:05 blah CORVETTE blah blah
> >

select em.incidentid, ea.recordtest as retdata
from    em
join ( -- equivalent to "where incidentid in (...)", sometimes faster.
      select incidentid
      from  em join  ea using (incidentid)
      where em.entrydate between '2005-1-1' and '2005-5-9'
      group by incidentid
      having 1 = min(case when recordtest like '%RED%' then 1 end)
         and 1 = min(case when recordtest like '%CORVETTE%' then 1 end)
     ) as X using (incidentid);