Re: Query tuning help

From: Mischa Sandberg <mischa(dot)sandberg(at)telus(dot)net>
To: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
Cc: Dan Harris <fbsd(at)drivefaster(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query tuning help
Date: 2005-05-09 17:31:58
Message-ID: 1115659918.427f9e8ee7789@webmail.telus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Quoting Russell Smith <mr-russ(at)pws(dot)com(dot)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);

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Derek Buttineau|Compu-SOLVE 2005-05-09 18:10:02 Re: ORDER BY Optimization
Previous Message Joel Fradkin 2005-05-09 17:30:36 Re: Need help to decide Mysql vs Postgres