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 8D58A8BA26E for ; Mon, 21 Feb 2005 03:02:15 +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 90421-03 for ; Mon, 21 Feb 2005 03:02:03 +0000 (GMT) Received: from gizmo02ps.bigpond.com (gizmo02ps.bigpond.com [144.140.71.12]) by svr1.postgresql.org (Postfix) with SMTP id BD2D08BA041 for ; Mon, 21 Feb 2005 03:02:00 +0000 (GMT) Received: (qmail 20988 invoked from network); 21 Feb 2005 03:01:52 -0000 Received: from unknown (HELO psmam05.bigpond.com) (144.135.25.81) by gizmo02ps.bigpond.com with SMTP; 21 Feb 2005 03:01:52 -0000 Received: from cpe-203-45-202-230.qld.bigpond.net.au ([203.45.202.230]) by psmam05.bigpond.com(MAM REL_3_4_2a 107/1293331) with SMTP id 1293331; Mon, 21 Feb 2005 13:01:52 +1000 Message-ID: <42194F00.3030704@bigpond.net.au> Date: Mon, 21 Feb 2005 13:01:20 +1000 From: David Brown User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: bad performances using hashjoin References: <15413.1108925170@sss.pgh.pa.us> In-Reply-To: <15413.1108925170@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.796 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200502/280 X-Sequence-Number: 10609 Tom Lane wrote: >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. > That's what I thought at first, but if you look more closely, that's having very little impact on either the cost or actual time: -> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=1) The real problem appears to be here: -> Hash Left Join (cost=16.14..89.16 rows=1196 width=159) (actual time=3.504..809.262 rows=1203 loops=1) As Gaetano points out in his follow-up post, the problem still exists after he removed the sorts: -> Hash Left Join (cost=16.14..80.19 rows=1196 width=4) (actual time=7.291..13.620 rows=1203 loops=1) The planner is not breaking up the outer join in his v_packages view: SELECT * FROM packages p LEFT OUTER JOIN package_security ps USING (id_package) It's not being selective at all with packages, despite id_package being the link to sat_request. If this is too complex for the planner, could he re-arrange his outer join so that's it's processed later? If he puts it in his actual query, for instance, will the planner flatten it out anyway?