Thread: optimizing a view-driven query

if i've got a view that joins three or four tables, is there a
way to SELECT on that view to bypass any of the joins if they're
not needed in the result?

    create view course as
        select
            _course.name as course,
            _topic.name  as topic,
            _school.name as school,
            _state.name  as state,
            _school.zip  as zip
        where
            _course.topic = _topic.id
            and
            _topic.school = _school.id
            and
            _state.abbr   = _school.state
        ;

    select * from course ;
    -- shows all fields via all tables

    select topic from course where course like '%comput%' ;
    -- not asking for state, we don't need to join the state table

here the optimizer might know we don't need to join the static
lookup '_state' table. can this be made to happen?

or is it just best to have "one view, one purpose"?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich <will@serensoft.com> writes:
>     select topic from course where course like '%comput%' ;
>     -- not asking for state, we don't need to join the state table

But you still do need to join, because the join affects which rows
will be returned.  The fact that you don't happen to use any values
out of one of the joined tables in your SELECT list is not very
relevant.

In this example, you might happen to know (or think you know) that there
will be one and only one state row matching any possible row from the
subjoin of the other tables, so in the end it wouldn't affect the number
of rows output.  This is not an assumption the planner is prepared to
make, however.

            regards, tom lane