Thread: Lazy View's Column Computing
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!
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
valueis 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
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.
--
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
@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; text-align:right; direction:rtl; unicode-bidi:embed; font-size:11.0pt; font-family:"Calibri",sans-serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;} 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.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!
-- 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