From: | Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | View efficiency questions |
Date: | 2004-09-12 11:21:56 |
Message-ID: | 41443154.2070302@chezphil.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | DU | 2004-09-12 14:13:46 | Network problem |
Previous Message | michael.wimmer | 2004-09-12 10:36:24 | Re: unicode and varchar |