X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 03FA49DC883 for ; Sat, 17 Dec 2005 05:15:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40176-04 for ; Sat, 17 Dec 2005 05:15:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by postgresql.org (Postfix) with ESMTP id ADFD29DC849 for ; Sat, 17 Dec 2005 05:15:52 -0400 (AST) Received: from [192.168.0.2] (unknown [84.12.183.55]) by smtp.nildram.co.uk (Postfix) with ESMTP id C5388269A07; Sat, 17 Dec 2005 09:15:51 +0000 (GMT) Subject: Re: Should Oracle outperform PostgreSQL on a complex From: Simon Riggs To: Bruce Momjian Cc: =?ISO-8859-1?Q?P=E5l?= Stenslet , pgsql-performance@postgresql.org In-Reply-To: <200512170428.jBH4S2W23715@candle.pha.pa.us> References: <200512170428.jBH4S2W23715@candle.pha.pa.us> Content-Type: text/plain Date: Sat, 17 Dec 2005 09:15:53 +0000 Message-Id: <1134810953.2964.117.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.017 required=5 tests=[AWL=0.017] X-Spam-Score: 0.017 X-Spam-Level: X-Archive-Number: 200512/423 X-Sequence-Number: 16244 On Fri, 2005-12-16 at 23:28 -0500, Bruce Momjian wrote: > How are star joins different from what we do now? Various ways of doing them, but all use plans that you wouldn't have come up with via normal join planning. Methods: 1. join all N small tables together in a cartesian product, then join to main Large table once (rather than N times) 2. transform joins into subselects, then return subselect rows via an index bitmap. Joins are performed via a bitmap addition process. You can fake (1) yourself with a temporary table, and the basics for (2) are now in place also. The characteristics of these joins make them suitable for large Data Warehouses with Fact-Dimension style designs. Many RDBMS have this, but we need to be careful of patent claims. I'm sure there's a way through that, but I'm not looking for it yet. Anybody else wishing to assist with a detailed analysis would be much appreciated. Best Regards, Simon Riggs