X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2D6A4329C67 for ; Sat, 25 Sep 2004 01:57:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79823-10 for ; Sat, 25 Sep 2004 00:57:16 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8683A329E66 for ; Sat, 25 Sep 2004 01:57:18 +0100 (BST) Received: from [134.22.69.205] (dyn-69-205.tor.dsl.tht.net [134.22.69.205]) by tht.net (Postfix) with ESMTP id CE4D276A11 for ; Fri, 24 Sep 2004 20:57:18 -0400 (EDT) Subject: Getting rid of nested loop From: Rod Taylor To: Postgresql Performance Content-Type: text/plain Message-Id: <1096073834.40463.41.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Fri, 24 Sep 2004 20:57:15 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/268 X-Sequence-Number: 8396 I set nested_loop = off, which is why I have the high cost. @ is a postgis operator between 2 geomotries (both polygons). It's the @ operator which is expensive. Is there a way to force a cheaper way of doing that join? -> Nested Loop (cost=100001905.94..100001906.08 rows=1 width=68) (actual time=1739.368..17047.422 rows=100 loops=1) Join Filter: ((COALESCE("outer".geom, "outer".geom) @ COALESCE("inner".geom, "inner".geom)) AND ("outer".region_id <> "inner".region_id))