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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
