Re: How to optimize monstrous query, sorts instead of

From: Tom Lane
Subject: Re: How to optimize monstrous query, sorts instead of
Date: ,
Msg-id: 3648.1056556509@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: How to optimize monstrous query, sorts instead of  (Rod Taylor)
List: pgsql-performance

Tree view

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 by date:

From: Josh Berkus
Date:
Subject: Re: Query running slower than same on Oracle
From: SZUCS Gábor
Date:
Subject: Re: Similar querys, better execution time on worst execution plan