Re: View efficiency questions

Phil Endecott <spam_from_postgresql_general@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=#