From: Tom Lane <tgl@sss.pgh.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
To: Pål Stenslet <paal.stenslet@exie.com>
To: pgsql-performance@postgresql.org
Subject: Re: Should Oracle outperform PostgreSQL on a complex
Date: Sat, 17 Dec 2005 13:47:07 -0500
Message-ID: <9010.1134845227@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, simon@2ndquadrant.com, pgman@candle.pha.pa.us, paal.stenslet@exie.com
Subject: Re: Should Oracle outperform PostgreSQL on a complex
In-Reply-To: <9010.1134845227@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox