Date: Mon, 09 May 2005 14:17:17 +1000
From: Klint Gore <kg@kgb.une.edu.au>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
In-Reply-To: <7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
References: <200505081906.47731.josh@agliodbs.com>
	<7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
Message-Id: <427EE44D37F.5629KG@129.180.47.120>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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             :                 :
+---------------------------------------+-----------------+