View efficiency questions

[Phil Endecott]

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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Phil Endecott <sp**************************@chezphil.org> writes:

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.

This would depend very largely on the details of V and on the details of
the calling query ... not to mention which Postgres version you are using.
In general I would not expect a trivial case to act that way (see
attached counterexample), so I suspect you haven't told us everything.
Give us a concrete example, please ...

regards, tom lane

regression=# create function f1(int) returns int language plpgsql as '
regression'# begin
regression'# raise notice ''f1: %'', $1;
regression'# return $1;
regression'# end';
CREATE FUNCTION
regression=# select * from int4_tbl;
f1
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)

regression=# create view v1 as select *,f1(f1) as func from int4_tbl;
CREATE VIEW
regression=# select * from v1;
NOTICE: f1: 0
NOTICE: f1: 123456
NOTICE: f1: -123456
NOTICE: f1: 2147483647
NOTICE: f1: -2147483647
f1 | func
-------------+-------------
0 | 0
123456 | 123456
-123456 | -123456
2147483647 | 2147483647
-2147483647 | -2147483647
(5 rows)

regression=# select * from v1 where f1 = 0;
NOTICE: f1: 0
f1 | func
----+------
0 | 0
(1 row)

regression=#

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Phil Endecott]

Hi Tom,

Thanks for writing. On Saturday I had a query that took 3000 ms but
reduced to only 1200 ms when I took out the date formatting functions
from the views. Now it takes only 700 ms with or without the functions.
Perhaps an ANALYZE in the meantime has changed everything.

Anyway, thanks for your example. The "raise notice" is a good debugging
idea. I will post again if the problem comes back and I can construct a
repeatable test case.

Regards,

--Phil.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster