From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Pål Stenslet <paal(dot)stenslet(at)exie(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Should Oracle outperform PostgreSQL on a complex |
Date: | 2005-12-17 18:47:07 |
Message-ID: | 9010.1134845227@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-12-17 19:03:54 | Re: Should Oracle outperform PostgreSQL on a complex |
Previous Message | Tom Lane | 2005-12-17 18:35:12 | Re: Should Oracle outperform PostgreSQL on a complex |