pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedview of view
3+ messages / 3 participants
[nested] [flat]
* view of view
@ 2005-12-08 02:47 Keith Worthington <KeithW@NarrowPathInc.com>
2005-12-08 13:46 ` Re: view of view Rich Doughty <rich@opusvl.com>
0 siblings, 1 reply; 3+ messages in thread
From: Keith Worthington @ 2005-12-08 02:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: view of view
2005-12-08 02:47 view of view Keith Worthington <KeithW@NarrowPathInc.com>
@ 2005-12-08 13:46 ` Rich Doughty <rich@opusvl.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rich Doughty @ 2005-12-08 13:46 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: view of view
@ 2005-12-08 13:13 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 3+ messages in thread
From: Merlin Moncure @ 2005-12-08 13:13 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-08 13:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-08 02:47 view of view Keith Worthington <KeithW@NarrowPathInc.com>
2005-12-08 13:46 ` Rich Doughty <rich@opusvl.com>
2005-12-08 13:13 Re: view of view Merlin Moncure <merlin.moncure@rcsonline.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox