From: Klint Gore <kg@kgb.une.edu.au>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Mon, 09 May 2005 14:17:17 +1000
Message-ID: <427EE44D37F.5629KG@129.180.47.120> (raw)
In-Reply-To: <7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
References: <200505081906.47731.josh@agliodbs.com>
<7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
On Sun, 8 May 2005 20:31:38 -0600, Dan Harris <fbsd@drivefaster.net> wrote:
> Duly noted. If this method can search across rows, I'm willing to
> accept this overhead for the speed it would add.
You could use intersect to search across rows. Using tsearch2 will look
up the RED and CORVETTE using the index and intersect will pull out the
commmon rows.
> In the meantime, is there any way I can reach my goal without Tsearch2
> by just restructuring my query to narrow down the results by date
> first, then seq scan for the 'likes'?
select distinct
em.incidentid,
ea.recordtext as retdata,
eg.long,
eg.lat
>from
ea, em, eg,
(
select
ea.incidentid
from
ea, em
where
em.incidentid = ea.incidentid and
em.entrydate >= '2005-1-1 00:00' and
em.entrydate <= '2005-5-9 00:00' and
recordtext like '%RED%'
intersect
select
ea.incidentid
from
ea, em
where
em.incidentid = ea.incidentid and
em.entrydate >= '2005-1-1 00:00' and
em.entrydate <= '2005-5-9 00:00' and
recordtext like '%CORVETTE%'
) as iid
where
em.incidentid = ea.incidentid and
em.incidentid = eg.incidentid and
em.entrydate >= '2005-1-1 00:00' and
em.entrydate <= '2005-5-9 00:00'
and ea.incidentid = iid.incidentid
and ( recordtext like '%RED%' or recordtext like '%CORVETTE%' )
order by em.entrydate
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
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: kg@kgb.une.edu.au, fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <427EE44D37F.5629KG@129.180.47.120>
* 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