Re: Lazy View's Column Computing

On Mon, 2 Aug 2021 at 19:53, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Avi Weinberg <AviW(at)gilat(dot)com> writes:
> > Is there a way to compute a column in a view only if it is referenced in
> the query? I have a view's column that its value is computed by a
> function. If in the query that column is not used at all, can Postgres
> "skip" computing it?
>
> If the function is not volatile, and you're using a moderately recent PG
> version, I'd expect the planner to do that for you.

something like this ?

postgres=# table t;
-[ RECORD 1 ]
col1 | 100
col2 | 100
col3 | 100

-- the sleep is intentional to
postgres=# create or replace function demo(int) returns int as $$
begin
perform pg_sleep(10);
return $1::int;
end; $$ language plpgsql immutable;
CREATE FUNCTION
Time: 7.253 ms

-- we create a view where col2 is a result of an immutable function call
from demo
postgres=# create or replace view myview as select col1, demo(col2)::int as
col2 from t;
CREATE VIEW
Time: 7.952 ms

postgres=# \x
Expanded display is off.
postgres=# explain (analyze,verbose) select col1, col2 from myview;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on public.t (cost=0.00..540.40 rows=2040 width=8) (actual
time=10010.231..10010.236 rows=1 loops=1)
Output: t.col1, demo(t.col2)
Query Identifier: 291510593965093899
Planning Time: 0.027 ms
Execution Time: 10010.250 ms -- the function demo was called which
resulted in slow exec time
(5 rows)

Time: 10010.648 ms (00:10.011)
postgres=# explain (analyze,verbose) select col1 from myview;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on public.t (cost=0.00..30.40 rows=2040 width=4) (actual
time=0.005..0.006 rows=1 loops=1)
Output: t.col1
Query Identifier: 8513308368843926789
Planning Time: 0.030 ms
Execution Time: 0.015 ms -- no function call as col2 not part of select
from view
(5 rows)

Time: 0.222 ms

> --
>
Thanks,
Vijay
Mumbai, India