I am creating a web app which has some complex underlying associations. In order to solve several issues I was having I created a UNION View. There are probably a lot of other ways this could be solved.
But I am now considering the efficiency of my design, and I wanted to know if a VIEW is newly created each time it is queried, or is it only created once, and kept updated.
To elaborate, if I have table_a (100 records) and table_b (100 records) and make a UNION View, then I have created a view with 200 records.
Does this whole process occur each time I do a select against the View?
Again, obviously each time I update the underlying table records the view is updated, but does the view update this one record or does it recreate the whole view from scratch?
Dale
A view is nothing more than a query with a name. There are possible perf-related optimizations, that some DBMS realize better than others (pgSQL seems to be on the better side), like reusing the query plan, cached access control etc.
However, at the end of they day, almost always, you can expect a view to behave like issuing the SQL directly. With the difference that you can grant access to this query w/o granting access to the underlying tables.
There are optimizations that you could do which change the behavior (make them half table-like) and that might or might not exist in pgSQL like materialized views (sorry no idea about pgSQL), but this is just nitpicking.
Use EXPLAIN to see how a VIEW is executed, you'll see the same results as a normal query.
EXPLAIN
SELECT * FROM name_of_your_view WHERE foo = 23;
PostgreSQL will try to optimize the inner query, even when you join views, have views using other views, etc. Try to avoid situations where a VIEW has to be executed before the optimizer can do it's (great) job. Aggregates, ORDER BY and LIMIT are examples of potential problems when using inside nested views. Just use EXPLAIN to see what is going on.
Does this whole process occur each time I do a select against the View?
Yes.
A non-materialized view (PostgreSQL doesn't support materialized views) is just a prepared SQL statement - you'd get the same performance by replacing a view reference with a subquery containing the SELECT that the view is based on.
This is why values based on the supporting tables appear every time you run a query on the view, I'm unclear on if column manipulations are made visible in PostgreSQL without refreshing the view - IE: If you create a view based on SELECT * FROM table_x, and then add or delete a column from table_x - most databases will require you to refresh the view to see that change via the view.
Building views on top of views should be discouraged - they're brittle; you won't know until running a view dependent on another if there's a problem. And there's no performance gain - rather the opposite. Code reuse does not perform well in a SET based environment...
