X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id E194430FC74 for ; Wed, 25 Jun 2003 15:55:23 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 63914-04 for ; Wed, 25 Jun 2003 12:55:13 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 7D33030FC8F for ; Wed, 25 Jun 2003 12:55:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5PFt9QQ003649; Wed, 25 Jun 2003 11:55:09 -0400 (EDT) To: Rod Taylor Cc: michael.mattox@verideon.com, Postgresql Performance Subject: Re: How to optimize monstrous query, sorts instead of In-reply-to: <1056548165.25587.23.camel@jester> References: <1056548165.25587.23.camel@jester> Comments: In-reply-to Rod Taylor message dated "25 Jun 2003 09:36:06 -0400" Date: Wed, 25 Jun 2003 11:55:09 -0400 Message-ID: <3648.1056556509@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/169 X-Sequence-Number: 2253 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