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 A390B8BAFB4 for ; Sun, 20 Feb 2005 18:46:20 +0000 (GMT) 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 51519-06 for ; Sun, 20 Feb 2005 18:46:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 26CF48BAF82 for ; Sun, 20 Feb 2005 18:46:14 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1KIkBZr015414; Sun, 20 Feb 2005 13:46:11 -0500 (EST) To: Gaetano Mendola Cc: pgsql-performance@postgresql.org Subject: Re: bad performances using hashjoin In-reply-to: References: Comments: In-reply-to Gaetano Mendola message dated "Sun, 20 Feb 2005 13:20:38 +0100" Date: Sun, 20 Feb 2005 13:46:10 -0500 Message-ID: <15413.1108925170@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/273 X-Sequence-Number: 10602 Gaetano Mendola writes: > If you need other info in order to improve the planner, ... like, say, the PG version you are using, or the definitions of the views involved? It's difficult to say much of anything about this. However: the reason the second plan wins is because there are zero rows fetched from sat_request, and so the bulk of the plan is never executed at all. I doubt the second plan would win if there were any matching sat_request rows. If this is the case you actually need to optimize, probably the thing to do is to get rid of the ORDER BY clauses you evidently have in your views, so that there's some chance of building a fast-start plan. BTW, I believe in 8.0 the first plan would be about as fast as the second, because we added some code to hash join to fall out without scanning the left input if the right input is empty. regards, tom lane