Re: Throwing unnecessary joins away

Hi,

I think it would be sufficient only for views. In other cases the
programmer can optimize himself. But a view can be a join of other
tables, and it is not sure that all of them are always needed. It all
depends on what I select from the view.
This information could even be calculted at view creation time. Of
cource this requires that views are handled in a bit more special way,
not just a view definition that is substituted into the original query
(as far as I know the current implementation is similar to this. Sorry
if not).
What do you think about this idea? Of course it is not trivial to
implement, but the result is really great.

Postgres could determine at creation time, if this kind of
optimization is possible at all or not. It can happan though that not
all information is available (I mean unique index or foreign key) at
that time. So this optimiztaion info could be refreshed later by a
command, "ALTER VIEW <viewname> ANALYZE" or "ANALYZE <view name>"
simply.
Postgres could also establish at creation time that for a given column
in the selection list which source table(s) are required. This is
probably not sufficient, but I haven't thought is through thouroughly
yet. And I am not that familiar with the current optimizer internals.
And one should be able to turn off this optimization, so that view
creation takes not longer than now. If the optimizer finds no
optimization info in the catalog, it behaves like now.
I hope you see this worth.
This all is analogue to statistics collection.

Thanks for reading,
Otto

2006/1/12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> =?ISO-8859-1?Q?Ott=F3_Havasv=F6lgyi?= <havasvolgyi(dot)otto(at)gmail(dot)com> writes:
> > As far as I know SQL Server has some similar feature. It does not join
> > if not necessary, more exactly: if the result would be the same if it
> > joined the table.
>
> I find it really really hard to believe that such cases arise often
> enough to justify having the planner spend cycles checking for them.
>
> regards, tom lane
>