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 566B63A2B23 for ; Tue, 16 Nov 2004 21:33:25 +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 01325-06 for ; Tue, 16 Nov 2004 21:33:14 +0000 (GMT) Received: from cmailg3.svr.pol.co.uk (cmailg3.svr.pol.co.uk [195.92.195.173]) by svr1.postgresql.org (Postfix) with ESMTP id E28F73A4016 for ; Tue, 16 Nov 2004 21:33:14 +0000 (GMT) Received: from modem-2555.llama.dialup.pol.co.uk ([217.135.185.251] helo=[192.168.0.102]) by cmailg3.svr.pol.co.uk with esmtp (Exim 4.41) id 1CUAwi-00023n-Kp; Tue, 16 Nov 2004 21:33:05 +0000 Subject: Re: mis-estimation on data-warehouse aggregate creation From: Simon Riggs To: Kris Jurka Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Organization: 2nd Quadrant Message-Id: <1100640596.4113.5680.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 16 Nov 2004 21:29:56 +0000 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: 200411/234 X-Sequence-Number: 9188 On Tue, 2004-11-16 at 09:10, Kris Jurka wrote: > By rewriting the JOIN > conditions to LEFT JOIN we force the planner to recognize that there will > be a match for every row in the sales table: > You realise that returns a different answer (or at least it potentially does, depending upon your data? > -> Hash Join (cost=4.70..194.23 rows=1 width=12) (actual time=2.675..74.693 rows=3288 loops=1) > Hash Cond: (("outer".monthnumber = "inner".monthnumber) AND ("outer".monthname = "inner".monthname) AND ("outer"."year" = "inner"."year") AND ("outer".monthyear = "inner".monthyear) AND ("outer".quarter = "inner".quarter) AND ("outer".quarteryear = "inner".quarteryear)) > -> Seq Scan on period (cost=0.00..90.88 rows=3288 width=54) (actual time=0.118..12.126 rows=3288 loops=1) > -> Hash (cost=3.08..3.08 rows=108 width=58) (actual time=1.658..1.658 rows=0 loops=1) > -> Seq Scan on shd_month (cost=0.00..3.08 rows=108 width=58) (actual time=0.081..0.947 rows=108 loops=1) ISTM your trouble starts here ^^^ estimate=1, but rows=3288 The join condition has so many ANDed predicates that we assume that this will reduce the selectivity considerably. It does not, and so you pay the cost dearly later on. In both plans, the trouble starts at this point. If you pre-build tables that have only a single join column between the full.oldids and shrunken.renumberedids then this will most likely work correctly, since the planner will be able to correctly estimate the join selectivity. i.e. put product.id onto shd_productline ahead of time, so you can avoid the complex join. Setting join_collapse_limit lower doesn't look like it would help, since the plan already shows joining the sub-queries together first. -- Best Regards, Simon Riggs