Re: How to optimize monstrous query, sorts instead of

Rod Taylor <rbt(at)rbt(dot)ca> 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