От: Klint Gore
Тема: Re: Query tuning help
Дата: ,
Msg-id: 427EE44D37F.5629KG@129.180.47.120
(см: обсуждение, исходный текст)
Ответ на: Re: Query tuning help  (Dan Harris)
Список: pgsql-performance

Скрыть дерево обсуждения

Query tuning help  (Dan Harris, )
 Re: Query tuning help  (Josh Berkus, )
 Re: Query tuning help  (Russell Smith, )
  Re: Query tuning help  (Tom Lane, )
  Re: Query tuning help  (Dan Harris, )
   Re: Query tuning help  (Josh Berkus, )
    Re: Query tuning help  (Dan Harris, )
     Re: Query tuning help  (Klint Gore, )
   Re: Query tuning help  (Tom Lane, )
   Re: Query tuning help  (Russell Smith, )
    Re: Query tuning help  (Dan Harris, )
    Re: Query tuning help  (Mischa Sandberg, )
 Re: Query tuning help  (Harald Fuchs, )
 Re: Query tuning help  (Ulrich Wisser, )

On Sun, 8 May 2005 20:31:38 -0600, Dan Harris <> 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   :            :  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             :                 :
+---------------------------------------+-----------------+


В списке pgsql-performance по дате сообщения:

От: Grega Bremec
Дата:
Сообщение: Re: sequence scan on PK
От: Harald Fuchs
Дата:
Сообщение: Re: Query tuning help