-1

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.

1 Answer 1

0

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?

2
  • I'm following you about the view just being a stored query that runs, but wouldn't that mean that two queries are ran in that scenario, first the query stored in the view and then the query that does a where id = 42 in your example? Jul 24, 2020 at 16:57
  • No, it's just a single query. The WHERE condition is simply appended to the view's query. Jul 24, 2020 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.