Re: Query tuning help

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