On Mon, 2 Aug 2021 at 19:53, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Avi Weinberg <AviW@xxxxxxxxx> 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
