Re: Query tuning help

On Sun, 8 May 2005 20:31:38 -0600, Dan Harris <fbsd(at)drivefaster(dot)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(at)kgb(dot)une(dot)edu(dot)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 : :
+---------------------------------------+-----------------+