Обсуждение: view of view

Hi All,

I am working on an application that uses PostgreSQL.  One of the
functions of the application is to generate reports.  In order to keep
the code in the application simple we create a view of the required data
in the database and then simply execute a SELECT * FROM
view_of_the_data;  All of the manipulation and most of the time even the
ordering is handled in the view.

My question is how much if any performance degradation is there in
creating a view of a view?

IOW if I have a view that ties together a couple of tables and
manipulates some data what will perform better; a view that filters,
manipulates, and orders the data from the first view or a view that
performs all the necessary calculations on the original tables?

--
Kind Regards,
Keith

> Hi All,
>
> I am working on an application that uses PostgreSQL.  One of the
> functions of the application is to generate reports.  In order to keep
> the code in the application simple we create a view of the required
data
> in the database and then simply execute a SELECT * FROM
> view_of_the_data;  All of the manipulation and most of the time even
the
> ordering is handled in the view.
>
> My question is how much if any performance degradation is there in
> creating a view of a view?
>
> IOW if I have a view that ties together a couple of tables and
> manipulates some data what will perform better; a view that filters,
> manipulates, and orders the data from the first view or a view that
> performs all the necessary calculations on the original tables?

very little, or a lot :).  Clear as mud?

Views in pg are built with the rule system which basically just expands
them into the source queries when it is time to execute them.  In my
experience, the time to expand the rule and generate the plan is trivial
next to actually running the query.

What you have to watch out for is if your plan is such that the lower
view has to be fully materialized in order for the lower query to
execute.  For example if you do some string processing on a key
expression, it obviously can no longer by used in an index expression.

A real simple way to do the materialization test is to do a select *
limit 1 from your view-on-view.  If it runs quickly, you have no
problems.

By the way, I consider views on views to be a good indicator of a good
design :).

Merlin

Keith Worthington wrote:
> Hi All,
>
> I am working on an application that uses PostgreSQL.  One of the
> functions of the application is to generate reports.  In order to keep
> the code in the application simple we create a view of the required data
> in the database and then simply execute a SELECT * FROM
> view_of_the_data;  All of the manipulation and most of the time even the
> ordering is handled in the view.
>
> My question is how much if any performance degradation is there in
> creating a view of a view?
>
> IOW if I have a view that ties together a couple of tables and
> manipulates some data what will perform better; a view that filters,
> manipulates, and orders the data from the first view or a view that
> performs all the necessary calculations on the original tables?

from personal experience, if the inner views contain outer joins performance
isn't that great.

--

   - Rich Doughty