Re: How to optimize monstrous query, sorts instead of

От: Rod Taylor
Тема: Re: How to optimize monstrous query, sorts instead of
Дата: ,
Msg-id: 1056543129.69651.82.camel@jester
(см: обсуждение, исходный текст)
Ответ на: Re: How to optimize monstrous query, sorts instead of using index  ("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, )

> Here's the output of explain analyze.  The query typically takes 0-4 seconds
> depending on the time frame.  It's run very frequently especially to process
> the nightly reports.

The plan picked seems reasonable (estimated costs / tuples is close to
actual).

I think the biggest hit is this index scan.  Thats a substantial cost to
pull out less than a thousand lines:

                           ->  Index Scan using monitorstatusxmonitori
on
monitorstatusx ms  (cost=0.00..4695.65 rows=880 width=83) (actual
time=40.17..1868.12 rows=625 loops=1)
                                 Index Cond: ("outer".jdoidx =
ms.monitorx)
                                 Filter: ((datex >= '2003-06-20
08:57:21.36'::timestamp without time zone) AND (datex <= '2003-06-29
08:57:21.36'::timestamp without time zone))


Are jdoidx and monitorx integers?

You might try a multi-column index on (ms.monitorx, ms.datex).

Are monitorx assigned roughly ordered by date?  It must be, otherwise
the sort step would not be so cheap (hardly any impact on the query --
see actual cost number).  The multi-column index above should give you a
bit of a boost.

Depending on the data in the table, the index (ms.datex, monitorx) may
give better results along with a single index on (ms.monitorx) as you
currently have.  It's not very likely though.

--
Rod Taylor <>

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

Вложения

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

От: Tom Lane
Дата:
Сообщение: Re: How to optimize monstrous query, sorts instead of using index
От: "Fernando Papa"
Дата:
Сообщение: Similar querys, better execution time on worst execution plan