How are database views executed?

Question

Lets assume there is a design where every table is represented by a view, that is simply selecting all of the columns of the table and all the rows.

--vw
Select * From sys_table

And I then run a query

Select * From vw Where some_column = some_value

Will the database first execute the query in the view, selecting all the data of that table into memory, like it would if I ran the query independently, and then will it run my actual query that has the where statement?

The reason I was wondering this is I was trying to understand how views work, and was wondering if the database does optimizations like combining queries to make sure it doesn't load the whole data in memory if it doesn't have to.

score 1 · Accepted Answer · 2020-07-24T16:39:07.850

A view is just a stored query, when you select from the view, the database runs the query that is associated with the view.

You can easily see that if you run explain (analyze) and check the execution plan.

Assume the following table & view:

create table data (id integer primary key, some_date date, some_number numeric);
create view v_data as select * from data;

When I run:

explain analyze 
select * 
from v_data
where id = 42;

I get the following execution plan:

Index Scan using data_pkey on data  (cost=0.15..8.17 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=1)
  Index Cond: (id = 42)
Planning Time: 0.104 ms
Execution Time: 0.023 ms

As you can see, the view's name does not even show up in the plan.

For some more insights on potential performance pitfalls, see Are views harmful for performance in PostgreSQL?

How are database views executed?

1 Answers1