From: Russell Smith <mr-russ@pws.com.au>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Mon, 9 May 2005 12:32:05 +1000
Message-ID: <200505091232.05495.mr-russ@pws.com.au> (raw)
In-Reply-To: <6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
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 AND em.entrydate >=
> > '2005-1-1 00:00'
> > AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like '%RED%'
> > AND ea.recordtext like '%CORVETTE%')
> > JOIN eg ON em.incidentid = eg.incidentid WHERE (recordtext like
> > '%RED%' or recordtext like '%CORVETTE%' );
> >
>
> I have run this, and while it is very fast, I'm concerned it's not
> doing what I need.
How fast is very fast?
> 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
>
> So, doing a search with an 'and' condition, e.g. WHERE RECORDTEXT LIKE
> '%RED%' AND RECORDTEXT LIKE '%CORVETTE%' , will not match because the
> condition will only be applied to a single row of recordtext at a time,
> not a whole group with the same incident number.
>
> If I were to use tsearch2 for full-text indexing, would I need to
> create another table that merges all of my recordtext rows into a
> single 'text' field type? If so, this is where I run into problems, as
> my logic also needs to match multiple words in their original order. I
> may also receive additional updates to the previous data. In that
> case, I need to replace the original record with the latest version of
> it. If I have already concatenated these rows into a single field, the
> logic to in-line replace only the old text that has changed is very
> very difficult at best. So, that's the reason I had to do two
> subqueries in my example. Please tell me if I misunderstood your logic
> and it really will match given my condition above, but it didn't seem
> like it would.
>
> Thanks again for the quick responses! This list has been a great
> resource for me.
>
select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat
FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >= '2005-1-1 00:00'
AND em.entrydate <= '2005-5-9 00:00' AND (ea.recordtext like '%RED%' OR ea.recordtext like '%CORVETTE%'))
JOIN eg ON em.incidentid = eg.incidentid WHERE
em.incidentid IN
(select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat
FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >= '2005-1-1 00:00'
AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like '%CORVETTE%'))
JOIN eg ON em.incidentid = eg.incidentid) AND
em.incidentid IN
(select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat
FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >= '2005-1-1 00:00'
AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like '%RED%'))
JOIN eg ON em.incidentid = eg.incidentid)
This may be more accurate. However I would cool it VERY NASTY. Josh's solutions may be better.
However much of the data should be in memory once the subplans are done, so it may be quite fast.
you may
> >
> -Dan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
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: mr-russ@pws.com.au, fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <200505091232.05495.mr-russ@pws.com.au>
* 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