Lazy View's Column Computing

Avi Weinberg <AviW@gilat.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.

regards, tom lane

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

Avi Weinberg <AviW@gilat.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

For all that we know, it may already be happening. That looks like a
pretty reasonable optimization which may already be in place. If we
create a view:

mgogala=# select * from dept;
�deptno |�� dname��� |�� loc
--------+------------+----------
���� 10 | ACCOUNTING | NEW YORK
���� 20 | RESEARCH�� | DALLAS
���� 30 | SALES����� | CHICAGO
���� 40 | OPERATIONS | BOSTON
(4 rows)

mgogala=# create view acct_view as select * from emp where deptno=10;
CREATE VIEW

The query from the view would probably merge view� with the original and
optimize everything as a single query. Unfortunately, there is no way to
tell:

mgogala=# explain select ename,job,sal from acct_view;
�������������������� QUERY PLAN
----------------------------------------------------
�Seq Scan on emp� (cost=0.00..1.18 rows=3 width=21)
�� Filter: (deptno = 10)
(2 rows)

The only tool that you have at your disposal is EXPLAIN. What we need to
ascertain that assumption is an optimizer trace file detailing the
decisions made by optimizer, something like the event 10053 from another
database which will remain unnamed. Merging the view query into the top
level query would produce something like this:

mgogala=# select ename,job,sal from emp
mgogala-# where deptno=10;
�ename� |��� job��� | sal
--------+-----------+------
�CLARK� | MANAGER�� | 2450
�KING�� | PRESIDENT | 5000
�MILLER | CLERK���� | 1300
(3 rows)

The table, shown below, has more columns than the 3 used in the above query:

mgogala=# \d emp
�������������������������� Table "mgogala.emp"
� Column� |����������� Type������������ | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
�empno��� | smallint������������������� |���������� | not null |
�ename��� | character varying(10)������ |���������� |��������� |
�job����� | character varying(9)������� |���������� |��������� |
�mgr����� | smallint������������������� |���������� |��������� |
�hiredate | timestamp without time zone |���������� |��������� |
�sal����� | double precision����������� |���������� |��������� |
�comm���� | double precision����������� |���������� |��������� |
�deptno�� | smallint������������������� |���������� |��������� |
Indexes:
��� "emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
��� "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

Merging the top level query with the view query would be smart tactic
which is probably already deployed. However, it is not possible to tell
with the tools at hand. That is what you want: the query touches only
the columns you need, nothing else. That is done by the query optimizer
in the "rewrite" phase of the query.

https://www.postgresql.org/docs/12/query-path.html

I could bet that the top level query gets merged with the view query
during the rewrite and that the columns that aren't needed aren't
touched. That in particular means that the function computing an
untouched column of the query isn't executed as it is.

Regards

Regards

On 8/2/21 10:12 AM, Avi Weinberg wrote:

Hi,

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?

Thanks!

IMPORTANT - This email and any attachments is intended for the above
named addressee(s), and may contain information which is confidential
or privileged. If you are not the intended recipient, please inform
the sender immediately and delete this email: you should not copy or
use this e-mail for any purpose nor disclose its contents to any person.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Mladen Gogala <gogala.mladen@gmail.com> writes:

The query from the view would probably merge view  with the original and
optimize everything as a single query. Unfortunately, there is no way to
tell:

mgogala=# explain select ename,job,sal from acct_view;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.18 rows=3 width=21)
   Filter: (deptno = 10)
(2 rows)

The only tool that you have at your disposal is EXPLAIN.

No ... EXPLAIN VERBOSE would fill in the detail you want,
as somebody else already demonstrated in this thread.

regards, tom lane