Re: How to optimize monstrous query, sorts instead of

От: Rod Taylor
Тема: Re: How to optimize monstrous query, sorts instead of
Дата: ,
Msg-id: 1056551304.25587.52.camel@jester
(см: обсуждение, исходный текст)
Ответ на: Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox")
Ответы: Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox")
Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox")
Список: pgsql-performance

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

How to optimize monstrous query, sorts instead of using index  ("Michael Mattox", )
 Re: How to optimize monstrous query, sorts instead of using index  (Rod Taylor, )
  Re: How to optimize monstrous query, sorts instead of using index  ("Michael Mattox", )
   Re: How to optimize monstrous query, sorts instead of  (Rod Taylor, )
    Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
     Re: How to optimize monstrous query, sorts instead of  (Rod Taylor, )
      Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
       Re: How to optimize monstrous query, sorts instead of  (Rod Taylor, )
        Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
        Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
         Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
          Re: How to optimize monstrous query, sorts instead of  (Rod Taylor, )
           Re: How to optimize monstrous query, sorts instead of  ("Michael Mattox", )
         Re: How to optimize monstrous query, sorts instead of  (Rod Taylor, )
       Re: How to optimize monstrous query, sorts instead of  (SZUCS Gábor, )
      Re: How to optimize monstrous query, sorts instead of  (Tom Lane, )
   Re: How to optimize monstrous query, sorts instead of using index  (SZUCS Gábor, )
    Re: How to optimize monstrous query, sorts instead of using index  ("Michael Mattox", )
     Re: How to optimize monstrous query, sorts instead of using index  (Tom Lane, )

> I didn't notice that before, thanks for pointing that out.  I just tried
> adding monitorx.idx to the select and it ended up making my query take
> several minutes long.  Any ideas how I can fix this and keep my performance?

By using it aliased and non-aliased (2 different references to the same
table) you've caused it to join itself.

Try this:

SELECT m.idx
     , ms.averageconnecttimex AS ms_averageconnecttime
     , ms.averagedurationx AS ms_averageduration
     , ms.datex AS ms_date
     , ms.idx AS ms_id
     , ms.statusstringx AS ms_statusstring
     , ms.statusx AS ms_status
     , msi.actualcontentx AS msi_actualcontent
     , msi.connecttimex AS msi_connecttime
     , msi.correctcontentx AS msi_correctcontent
     , msi.datex AS msi_date
     , msi.descriptionx AS msi_description
     , msi.durationx AS msi_duration
     , msi.errorcontentx AS msi_errorcontent
     , msi.idx AS msi_id
     , msi.monitorlocationx AS msi_monitorlocation
     , msi.statusstringx AS msi_statusstring
     , msi.statusx AS msi_status

  FROM monitorstatusx AS ms
     , monitorstatusitemx AS msi

     , monitorx AS mx
     , monitorstatus_statusitemsx AS mssisx
     , monitorstatusitemlistd8ea58a5x AS litem

 WHERE ms.jdoidx = mssisx.jdoidx
   AND mssisx.statusitemsx = litem.jdoidx
   AND litem.statusitemlistx = msi.jdoidx
   AND mx.jdoidx = ms.monitorx
   AND ms.datex BETWEEN '2003-06-20 08:57:21.36'
                    AND '2003-06-29 08:57:21.36'
   AND m.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'

ORDER BY ms.datex DESC;

--
Rod Taylor <>

PGP Key: http://www.rbt.ca/rbtpub.asc

Вложения

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

От: "Fernando Papa"
Дата:
Сообщение: Similar querys, better execution time on worst execution plan
От: "Sailer, Denis (YBUSA-CDR)"
Дата:
Сообщение: Query running slower than same on Oracle