I spent a little time staring at the v5 patches. Obviously there
are a bunch of minor details to be verified, which you've carefully
provided XXX comments about, and I didn't really go through those
yet. There are two big-picture questions that are bothering me:
1. I do not think I believe the premise that the dimension tables
typically won't have restriction clauses. ISTM that a typical
query might be like
select sum(o.total_price) from
orders o
join customers c on c.id = o.c_id
join products p on p.id = o.p_id
where c.customer_name = 'Wile E Coyote'
and p.product_name = 'Rocket Skates';
The only reason to join a dimension table that lacks a restriction
clause is if you need some of its fields in the output, which you
might but I'm not sure that's such a common case. (Have you got
evidence to the contrary?) So I feel like we're not going to be
getting all that much win if we are not willing to treat such tables
as dimension tables. We could do something simplistic like order
those dimensions by the selectivity of their baserestrict clauses,
joining the most-restricted ones first and any restriction-free ones
last.
2. I'm pretty un-excited about the 0002 patch altogether. I'm having
a hard time visualizing cases where it helps, other than left joins
to dimension tables which I don't really think are common either.
I did a bit of poking around on the net and found that it seems to
be common to restrict star-join optimizations to equijoins (e.g.
SAP says explicitly that they only handle that case). I think we'd
be better off to focus on the allow-baserestrict-clauses extension
than the allow-join-order-restrictions extension.
regards, tom lane
