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 F1D849DCAC0 for ; Sat, 17 Dec 2005 14:47:12 -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 28253-05 for ; Sat, 17 Dec 2005 14:47:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 13DF69DC88F for ; Sat, 17 Dec 2005 14:47:09 -0400 (AST) 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 jBHIl7ET009011; Sat, 17 Dec 2005 13:47:07 -0500 (EST) To: Simon Riggs , Bruce Momjian , =?ISO-8859-1?Q?P=E5l?= Stenslet , pgsql-performance@postgresql.org Subject: Re: Should Oracle outperform PostgreSQL on a complex In-reply-to: <8627.1134843222@sss.pgh.pa.us> References: <200512170428.jBH4S2W23715@candle.pha.pa.us> <1134810953.2964.117.camel@localhost.localdomain> <8627.1134843222@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Sat, 17 Dec 2005 13:13:42 -0500" Date: Sat, 17 Dec 2005 13:47:07 -0500 Message-ID: <9010.1134845227@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.013 required=5 tests=[AWL=0.013] X-Spam-Score: 0.013 X-Spam-Level: X-Archive-Number: 200512/429 X-Sequence-Number: 16250 I wrote: > However, I submit that it wouldn't pick such a plan anyway, and should > not, because the idea is utterly stupid. BTW, some experimentation suggests that in fact a star join is already slower than the "regular" plan in 8.1. You can force a star-join plan to be generated like this: regression=# set join_collapse_limit TO 1; SET regression=# explain select * from fact,d1 cross join d2 where fact.f1=d1.f1 and fact.f2=d2.f1; QUERY PLAN --------------------------------------------------------------------------- Hash Join (cost=4.71..8238.71 rows=102400 width=16) Hash Cond: (("outer".f1 = "inner".f1) AND ("outer".f2 = "inner".f1)) -> Seq Scan on fact (cost=0.00..1578.00 rows=102400 width=8) -> Hash (cost=4.21..4.21 rows=100 width=8) -> Nested Loop (cost=1.11..4.21 rows=100 width=8) -> Seq Scan on d1 (cost=0.00..1.10 rows=10 width=4) -> Materialize (cost=1.11..1.21 rows=10 width=4) -> Seq Scan on d2 (cost=0.00..1.10 rows=10 width=4) (8 rows) and at least in the one test case I tried, this runs slower than the nested-hash plan. EXPLAIN ANALYZE misleadingly makes it look faster, but that's just because of the excessive per-plan-node ANALYZE overhead. Try doing something like \timing select count(*) from fact, ... to get realistic numbers. regards, tom lane