From: Mischa Sandberg <mischa.sandberg@telus.net>
To: Russell Smith <mr-russ@pws.com.au>
Cc: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Mon, 9 May 2005 10:31:58 -0700
Message-ID: <1115659918.427f9e8ee7789@webmail.telus.net> (raw)
In-Reply-To: <200505091232.05495.mr-russ@pws.com.au>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
<200505091232.05495.mr-russ@pws.com.au>
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mischa.sandberg@telus.net, mr-russ@pws.com.au, fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <1115659918.427f9e8ee7789@webmail.telus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox