Re: View efficiency questions

Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)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=#