Re: How to optimize monstrous query, sorts instead of

От: Tom Lane
Тема: Re: How to optimize monstrous query, sorts instead of
Дата: ,
Msg-id: 3648.1056556509@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: How to optimize monstrous query, sorts instead of  (Rod Taylor)
Список: 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, )

Rod Taylor <> writes:
>> monitorstatusx_datex_monitorx_index on monitorstatusx ms
>> (cost=3D0.00..1159.33 rows=3D890 width=3D83) (actual time=3D0.19..1287.02=
>  rows=3D628
>> loops=3D1)
>> Index Cond: (("outer".jdoidx =3D ms.moni=
> torx)
>> AND (ms.datex >=3D '2003-06-20 08:57:21.36'::timestamp without time zone)=
>  AND
>> (ms.datex <=3D '2003-06-29 08:57:21.36'::timestamp without time zone))

> You can see that it used the new multi-key index for both items, rather
> than finding for monitorx, then filtering out unwanted results by datex.

What is the column ordering of the combined index?  Unless datex is the
first column, there is no chance of using it to create the required sort
order anyway.  I think this index condition is suggesting that monitorx
is the first column.

However, I agree with Rod's point that "avoid the sort" is not the
mindset to use to optimize this query. The joins are the problem.
You might try forcing different join types (see enable_nestloop and
friends) to get an idea of whether a different plan is likely to help.

            regards, tom lane


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

От: Josh Berkus
Дата:
Сообщение: Re: Query running slower than same on Oracle
От: SZUCS Gábor
Дата:
Сообщение: Re: Similar querys, better execution time on worst execution plan