Dear Experts,
I have a couple of questions about the efficiency of queries involving
views.
Say I have a large table T, and a view V that just adds some extra
columns to T, using for example some date-to-text formatting functions.
The functions are defined as immutable. Now I "select * from V where
pkey=xxxxx". My hope was that the "where" filter would run on the table
T and the functions would only run on the single row that is returned.
Instead it looks as if the functions are applied to every row, i.e. V is
completely built, and then the one row is selected. (In contrast, if I
don't use a view but put the functions in the select, I think that they
are run only for the selected row.)
Is this the expected behaviour? I can supply a more detailed example if
it would help.
The second case is similar though a little more complex. This time,
rather than immutable functions adding extra columns in the view, it is
joins. For example, T might have codes which are expanded to
human-readable descriptions by joining with a code-to-description table.
Again I select a single row using T's primary key, and hope that the
code-to-description is only done for that one row, but instead it seems
to be done for every row.
Thoughts, anyone?
Regards,
--Phil Endecott.
