Re: Query tuning help - Mailing list pgsql-performance

From Mischa Sandberg
Subject Re: Query tuning help
Date
Msg-id 1115659918.427f9e8ee7789@webmail.telus.net
Whole thread Raw
In response to Re: Query tuning help  (Russell Smith <mr-russ@pws.com.au>)
List pgsql-performance
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);



pgsql-performance by date:

Previous
From: William Yu
Date:
Subject: Re: Whence the Opterons?
Next
From: Derek Buttineau|Compu-SOLVE
Date:
Subject: Re: ORDER BY Optimization