Thread: Views- Advantages and Disadvantages
Hello All,
Can anybody please point me to Advantages and Disadvantages of using view
With Regards
Ashish...
On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote: > Hello All, Hi, > Can anybody please point me to Advantages and Disadvantages > of using view The most obvious advantage (for me, anyway) is to restrict which columns a user can see in a table. I'm sure there are others. > With Regards > Ashish... Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm
Thanks Andrej
for your replay
I have found out some of them n are as follows, but I want more reasons
for not using views . I only got one
Advantages:
1) Permission to user can be given to access the database only
through view containing specific data the user is authorized to see
2) View convert multiple table queries into single table
queries
3) Views give personalized view
4) Views provide consistent unchanged image even if the underlying
table columns changed.
Disadvantages:
1) Performance : If a view is defined by complex multitable query,then
simple query against that view becomes a coplecated join, and it may take a
long time to complete
----- Original Message -----
From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com>
To: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>; "Postgres
General" <pgsql-general@postgresql.org>
Sent: Wednesday, May 09, 2007 12:08 PM
Subject: Re: [GENERAL] Views- Advantages and Disadvantages
> On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote:
>> Hello All,
> Hi,
>
>> Can anybody please point me to Advantages and Disadvantages
>> of using view
> The most obvious advantage (for me, anyway) is to restrict which
> columns a user can see in a table.
>
> I'm sure there are others.
>
>
>
>
>> With Regards
>> Ashish...
> Cheers,
> Andrej
>
>
> --
> Please don't top post, and don't use HTML e-Mail :} Make your quotes
> concise.
>
> http://www.american.edu/econ/notes/htmlmail.htm
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
Thanks Brent for your replay, What about the Disadvantages, Performance issues? With Regards Ashish... ----- Original Message ----- From: <woodb@niwa.co.nz> To: "Postgres General" <pgsql-general@postgresql.org> Cc: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com> Sent: Wednesday, May 09, 2007 1:24 PM Subject: Re: [GENERAL] Views- Advantages and Disadvantages >> On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote: >> >> Hello All, >> Can anybody please point me to Advantages and Disadvantages >> of using view > > Hi Ashish, > > There are several, but they are generally about implementing a more user > friendly database from a well normalised structure. > > > You can use views to do any or all of: > > act as a filter on fields or records which can be accessed by given users, > > act as a stored query to avoid writing a commonly used SQL, > > aggregate normalised tables into a single virtual table, so that access > does not require as complex a join, thus it can hide the complexity of the > database structure from applications or users. > > An example use is to have a view which provides only the current version > of data from a database which stores multiple versions of the data. > > Cheers, > > Brent Wood > > ----- Original Message ----- From: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com> To: "Andrej Ricnik-Bay" <andrej.groups@gmail.com> Cc: "Postgres General" <pgsql-general@postgresql.org> Sent: Wednesday, May 09, 2007 12:46 PM Subject: Re: [GENERAL] Views- Advantages and Disadvantages > Thanks Andrej > for your replay > > I have found out some of them n are as follows, but I want more reasons > for not using views . I only got one > > Advantages: > > 1) Permission to user can be given to access the database only > through view containing specific data the user is authorized to see > > 2) View convert multiple table queries into single table > queries > > 3) Views give personalized view > > 4) Views provide consistent unchanged image even if the underlying > table columns changed. > > > > Disadvantages: > > > > 1) Performance : If a view is defined by complex multitable > query,then > simple query against that view becomes a coplecated join, and it may take > a > long time to complete > > > > > > > > > ----- Original Message ----- > From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com> > To: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>; "Postgres > General" <pgsql-general@postgresql.org> > Sent: Wednesday, May 09, 2007 12:08 PM > Subject: Re: [GENERAL] Views- Advantages and Disadvantages > > >> On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote: >>> Hello All, >> Hi, >> >>> Can anybody please point me to Advantages and Disadvantages >>> of using view >> The most obvious advantage (for me, anyway) is to restrict which >> columns a user can see in a table. >> >> I'm sure there are others. >> >> >> >> >>> With Regards >>> Ashish... >> Cheers, >> Andrej >> >> >> -- >> Please don't top post, and don't use HTML e-Mail :} Make your quotes >> concise. >> >> http://www.american.edu/econ/notes/htmlmail.htm >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/09/07 02:16, Ashish Karalkar wrote: [snip] > Disadvantages: > > > > 1) Performance : If a view is defined by complex multitable > query,then simple query against that view becomes a coplecated > join, and it may take a long time to complete I don't see that as relevant, since we know which objects are tables and which are views. But maybe that's just our site. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQYL9S9HxQb37XmcRAqhIAKDTiOQpvifDk5nVU87MvGOjLqdV8wCffjf5 3lzftdxJzUDVhNaD9GME3VQ= =51Vx -----END PGP SIGNATURE-----
On 5/9/07, Ron Johnson <ron.l.johnson@cox.net> wrote: > > 1) Performance : If a view is defined by complex multitable > > query,then simple query against that view becomes a coplecated > > join, and it may take a long time to complete > I don't see that as relevant, since we know which objects are tables > and which are views. But maybe that's just our site. Plus, to retrieve the same information w/o the view he'd still have to have the same multi-table query which would a) take as long to run and b) take much longer to type :) Cheers, Andrej
Thanks All for your replies, But then dont we have any disadvantage of using View??? With Reagrds Ashish... ----- Original Message ----- From: "Andrej Ricnik-Bay" <andrej.groups@gmail.com> To: "Ron Johnson" <ron.l.johnson@cox.net> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, May 09, 2007 2:03 PM Subject: Re: [GENERAL] Views- Advantages and Disadvantages > On 5/9/07, Ron Johnson <ron.l.johnson@cox.net> wrote: >> > 1) Performance : If a view is defined by complex multitable >> > query,then simple query against that view becomes a coplecated >> > join, and it may take a long time to complete >> I don't see that as relevant, since we know which objects are tables >> and which are views. But maybe that's just our site. > Plus, to retrieve the same information w/o the view he'd > still have to have the same multi-table query which would > a) take as long to run and b) take much longer to type :) > > > Cheers, > Andrej > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On mið, 2007-05-09 at 12:46 +0530, Ashish Karalkar wrote: > I have found out some of them n are as follows, but I want more reasons > for not using views . I only got one > > Disadvantages: > 1) Performance : If a view is defined by complex multitable query,then > simple query against that view becomes a coplecated join, and it may take a > long time to complete > 2) UPDATES on a view are more tricky. gnari
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/09/07 03:36, Ashish Karalkar wrote: > Thanks All for your replies, > But then dont we have any disadvantage of using View??? You can't insert into multi-table views. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQYrNS9HxQb37XmcRAs+uAJ9cVMufkVvGiAFxS6saK0E0B8jOFwCfYYVP tHQ/Pf7jbmuy7IwdQkneRiU= =P0t3 -----END PGP SIGNATURE-----
Ok. That is on insert update delete part. ant disadvantage on select part? With Regards Ashish... ----- Original Message ----- From: "Ron Johnson" <ron.l.johnson@cox.net> To: <pgsql-general@postgresql.org> Sent: Wednesday, May 09, 2007 2:18 PM Subject: Re: [GENERAL] Views- Advantages and Disadvantages > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 05/09/07 03:36, Ashish Karalkar wrote: >> Thanks All for your replies, >> But then dont we have any disadvantage of using View??? > > You can't insert into multi-table views. > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Give a man a fish, and he eats for a day. > Hit him with a fish, and he goes away for good! > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFGQYrNS9HxQb37XmcRAs+uAJ9cVMufkVvGiAFxS6saK0E0B8jOFwCfYYVP > tHQ/Pf7jbmuy7IwdQkneRiU= > =P0t3 > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/09/07 03:48, Ashish Karalkar wrote: > Ok. That is on insert update delete part. > ant disadvantage on select part? Performance-wise? No. Both an advantage and disadvantage of views is that it codifies certain queries in the database. P.S. - top-posting is rude. > ----- Original Message ----- From: "Ron Johnson" <ron.l.johnson@cox.net> > To: <pgsql-general@postgresql.org> > Sent: Wednesday, May 09, 2007 2:18 PM > Subject: Re: [GENERAL] Views- Advantages and Disadvantages > > > On 05/09/07 03:36, Ashish Karalkar wrote: >>>> Thanks All for your replies, >>>> But then dont we have any disadvantage of using View??? > > You can't insert into multi-table views. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQY5QS9HxQb37XmcRAmgZAJ9LiKaJHwsPlTnZi9UpbiqEANpfdACaAqeS KtJSyTH7FcWp4JOna9BqNkc= =EBCc -----END PGP SIGNATURE-----
> On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote: > > Hello All, > Can anybody please point me to Advantages and Disadvantages > of using view Hi Ashish, There are several, but they are generally about implementing a more user friendly database from a well normalised structure. You can use views to do any or all of: act as a filter on fields or records which can be accessed by given users, act as a stored query to avoid writing a commonly used SQL, aggregate normalised tables into a single virtual table, so that access does not require as complex a join, thus it can hide the complexity of the database structure from applications or users. An example use is to have a view which provides only the current version of data from a database which stores multiple versions of the data. Cheers, Brent Wood
On 5/9/07, Ashish Karalkar <ashish.karalkar@info-spectrum.com> wrote: > Thanks Brent for your replay, > > > What about the Disadvantages, Performance issues [of views] Views are pretty much neutral from a performance perspective. There are certain small considerations here and there to think about but you should really be focusing on how they impact your development and design strategies. Probably there are two disadvantages of using views. The first is that the introduce dependencies on your tables so that whenever you need to certain things to your table you have to drop the view, do the thing (like drop a column) and re-create the view. Second issue is that views are not updatable without the introduction of rules which are extra dependencies and can be tricky to write. Now the first problem is not really a problem...in fact, views are a great way to tell the dba what applications are doing and gives him a chance to introduce application compatibility if the table structures change. So I count this as a feature of using views although it will feel like extra work to do, especially in the beginning. At some point, views (especially trivial ones) in PostgreSQL will become automatically updatable which will mitigate the impact of the second disadvantage. If and when that happens, IMNSHO, Views are a critical foundation to good development. Complex queries drifting around in various applications is A Bad Thing, and views allow you to control this and provide much better encapsulation of the database and exercise control over what's going on. Many application developers are uncomfortable with the idea of views (and other high level database features like functions) but when used properly, the benefits they can bring to your development and maintenance can be really dramatic. Use them. merlin
On May 9, 2007, at 4:36 AM, Ashish Karalkar wrote:
Thanks All for your replies,But then dont we have any disadvantage of using View???With ReagrdsAshish...
I once inherited a database that made extensive use of constants in views (a la magic numbers) as well as had several instances of complex views referencing other complex views. The magic numbers made it extremely difficult at first understand what was going on with the entire application (both on server and client) because it appeared that I was getting values out of thin air that were not coming from any tables or the client. The views-referencing-other-views made changing a view several layers up in the dependency hierarchy a real PITA as I had to completely drop all the views and re-add them.
Views are powerful, but with great power comes great responsibility. They can be very easily abused IMHO.
Ilan Volow
Implicit code is inherently evil, and here's the reason why:
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Ashish Karalkar > Sent: Wednesday, May 09, 2007 1:36 AM > To: Andrej Ricnik-Bay; Ron Johnson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Views- Advantages and Disadvantages > > Thanks All for your replies, > > But then dont we have any disadvantage of using View??? Views can hide important information from the optimizer (especially index information).
On May 9, 2007, at 14:02 , Dann Corbit wrote: > Views can hide important information from the optimizer (especially > index information). Really? AIUI, views—at least in PostgreSQL—are implemented using PostgreSQL's rule system: the entire query is rewritten to include the view query, and the optimizer sees the rewritten query. What the optimizer sees is the same as if the view were inlined in the original query. Michael Glaesemann grzm seespotcode net
> -----Original Message----- > From: Michael Glaesemann [mailto:grzm@seespotcode.net] > Sent: Wednesday, May 09, 2007 12:14 PM > To: Dann Corbit > Cc: Ashish Karalkar; Andrej Ricnik-Bay; Ron Johnson; pgsql- > general@postgresql.org > Subject: Re: [GENERAL] Views- Advantages and Disadvantages > > > On May 9, 2007, at 14:02 , Dann Corbit wrote: > > > Views can hide important information from the optimizer (especially > > index information). > > Really? AIUI, views-at least in PostgreSQL-are implemented using > PostgreSQL's rule system: the entire query is rewritten to include > the view query, and the optimizer sees the rewritten query. What the > optimizer sees is the same as if the view were inlined in the > original query. That is a significant achievement, since many database systems do not have that ability.
Dann Corbit wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Ashish Karalkar
>> Sent: Wednesday, May 09, 2007 1:36 AM
>> To: Andrej Ricnik-Bay; Ron Johnson
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Views- Advantages and Disadvantages
>>
>> Thanks All for your replies,
>>
>> But then dont we have any disadvantage of using View???
>
> Views can hide important information from the optimizer (especially
> index information).
I don't believe that is true in PostgreSQL's case.
Joshua D. Drake
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
>>> Views can hide important information from the optimizer (especially
>>> index information).
>> Really? AIUI, views-at least in PostgreSQL-are implemented using
>> PostgreSQL's rule system: the entire query is rewritten to include
>> the view query, and the optimizer sees the rewritten query. What the
>> optimizer sees is the same as if the view were inlined in the
>> original query.
>
> That is a significant achievement, since many database systems do not
> have that ability.
Another advantage of views is the ability to give zero rights to access
the objects the view is on, but allow the user to execute the view
itself. This allows limiting the view to not only a specific where
clause but also specific columns the user will be able to view.
Joshua D. Drake
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, 2007-05-09 at 12:02 -0700, Dann Corbit wrote:
> Views can hide important information from the optimizer (especially
> index information).
I believe that you're mistaken, and you can see it rather easily by
explaining a select on a view (or even a view of views). For example:
rkh@csb-dev=> \d palias
View "unison.palias"
Column | Type | Modifiers
-------------+--------------------------+-----------
palias_id | integer |
pseq_id | integer |
origin_id | integer |
alias | text |
descr | text |
tax_id | integer |
ref_pseq_id | integer |
added | timestamp with time zone |
View definition:
SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias,
pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added
FROM paliasorigin pa, pseqalias pv
WHERE pv.palias_id = pa.palias_id AND pv.is_current = true;
rkh@csb-dev=> explain select * from palias where tax_id=9606;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------
Hash Join (cost=701397.95..1634572.27 rows=1293890 width=137)
Hash Cond: (pv.palias_id = pa.palias_id)
-> Seq Scan on pseqalias pv (cost=0.00..474670.85
rows=20706650 width=20)
Filter: is_current
-> Hash (cost=647199.80..647199.80 rows=1692012 width=121)
-> Bitmap Heap Scan on paliasorigin pa
(cost=33808.65..647199.80 rows=1692012 width=121)
Recheck Cond: (tax_id = 9606)
-> Bitmap Index Scan on paliasorigin_tax_id_idx
(cost=0.00..33385.65 rows=1692012 width=0)
Index Cond: (tax_id = 9606)
(9 rows)
Long ago I compared a few views with their inlined counterparts and the
upshot is that there is exactly or practically zero difference.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
--- Reece Hart <reece@harts.net> wrote: > I believe that you're mistaken, and you can see it rather easily by > explaining a select on a view (or even a view of views). For example: > View definition: > SELECT pa.palias_id, pv.pseq_id, pa.origin_id, pa.alias, > pa.descr, pa.tax_id, pv.ref_pseq_id, pv.added > FROM paliasorigin pa, pseqalias pv > WHERE pv.palias_id = pa.palias_id AND pv.is_current = true; if you're only selectivity is "is_current = true", you will get a seq. scan no matter what. However, I bet your explain plan would be much better if you added something like: SELECT * FROM View WHERE origin_id = <some origin_id value>; as long as you had an index on origin_id. Regards, Richard Broersma Jr.
Ashish Karalkar wrote: > Thanks Brent for your replay, > > > What about the Disadvantages, Performance issues? As far as I'm aware, performance is the only real disadvantage. I tend to break DB design into stages: ER modelling to define the entities/relationships the DB needs to store/represent Normalize this to maximise data integrity & minimise duplication/redundancy De-normalise to meet performance & access requirements. In the latter stage, views are generally used, unless there are performance restrictions, when a de-normalised schema may be applied. Cheers, Brent
Brent Wood wrote:
> Ashish Karalkar wrote:
>> Thanks Brent for your replay,
>>
>>
>> What about the Disadvantages, Performance issues?
>
> As far as I'm aware, performance is the only real disadvantage.
What performance are we talking about here? Executing from a view
although has *some* overhead, I don't even know that it is worth
considering in most cases.
Joshua D. Drake
>
> I tend to break DB design into stages:
>
> ER modelling to define the entities/relationships the DB needs to
> store/represent
> Normalize this to maximise data integrity & minimise duplication/redundancy
> De-normalise to meet performance & access requirements.
>
>
> In the latter stage, views are generally used, unless there are
> performance restrictions,
> when a de-normalised schema may be applied.
>
> Cheers,
>
> Brent
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/09/07 15:18, Dann Corbit wrote: [snip] > > That is a significant achievement, since many database systems do not > have that ability. Maybe (probably!) back in the Oracle 6 days, but cost-based optimizers have done this for *years*. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQnAfS9HxQb37XmcRAlAUAKCb0GVM3ciXvoY6ACu8z7rl6ntTPwCgzP9S 8taWNM58VyBMaChvOfuVKaE= =7OGo -----END PGP SIGNATURE-----
On May 9, 2007, at 19:58 , Joshua D. Drake wrote: > Brent Wood wrote: >> Ashish Karalkar wrote: >>> Thanks Brent for your replay, >>> >>> >>> What about the Disadvantages, Performance issues? >> As far as I'm aware, performance is the only real disadvantage. > > What performance are we talking about here? Executing from a view > although has *some* overhead, I don't even know that it is worth > considering in most cases. Two people now have stated without much qualification that views have some kind of associated performance (Brent Woods) or optimization (Dann Corbit) penalty. Where does this idea come from? Views in PostgreSQL are just rewritten with the view query inlined! There's not much overhead there AIUI. Michael Glaesemann grzm seespotcode net
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Ron Johnson > Sent: Wednesday, May 09, 2007 6:07 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Views- Advantages and Disadvantages > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 05/09/07 15:18, Dann Corbit wrote: > [snip] > > > > That is a significant achievement, since many database systems do not > > have that ability. > > Maybe (probably!) back in the Oracle 6 days, but cost-based > optimizers have done this for *years*. I work mostly with legacy database systems, so I am surely behind the times here.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/09/07 20:21, Dann Corbit wrote: >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] On Behalf Of Ron Johnson >> Sent: Wednesday, May 09, 2007 6:07 PM >> To: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] Views- Advantages and Disadvantages >> >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On 05/09/07 15:18, Dann Corbit wrote: >> [snip] >>> That is a significant achievement, since many database systems do > not >>> have that ability. >> Maybe (probably!) back in the Oracle 6 days, but cost-based >> optimizers have done this for *years*. > > I work mostly with legacy database systems, so I am surely behind the > times here. As do I, but it (Rdb/VMS) has had a CBO for 20+ years. (Oracle bought Rdb/VMS from DEC back in 1994, and I know for certain that a lot of the Rdb engineers were transferred straight into the RDBMS engineering group.) - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQnYVS9HxQb37XmcRAjODAKCoiEXClQ9PyWT9sEGfn21qbRvRfwCgnXYM oYUrnVBwuSadIJfDDEDln2I= =i2pZ -----END PGP SIGNATURE-----
Joshua D. Drake wrote: > Brent Wood wrote: >> Ashish Karalkar wrote: >>> Thanks Brent for your replay, >>> >>> >>> What about the Disadvantages, Performance issues? >> >> As far as I'm aware, performance is the only real disadvantage. > > What performance are we talking about here? Executing from a view > although has *some* overhead, I don't even know that it is worth > considering in most cases. Sorry, I didn't express that clearly... A view as a filter on a single table, or a simple two table join is generally quite fast enough, and has no noticeable overhead over a query. A complex self relation plus some joins instantiated via a view can be slow. But a query to produce the same output from the same underlying tables will be just as slow. In such cases, (like some data warehousing) a redesigned, denormalised table structure can be implemented to provide the same "look" as the view would have, but as a physical table, well indexed and much faster than the view, without the joins being required. My comment was meant to indicate that a complex view wihich is too slow may be replaced by a denormalised physical table, which is faster, not that a view is significantly slower than the same query upon the same tables. To paraphrase, the view/query performance is dependent on the underlying table structures & relationships, and these are what may need to change if a view is too slow. In many cases, such changes can make the original view redundant. Cheers, Brent
On Wednesday 9. May 2007 06:32, Ashish Karalkar wrote:
>Hello All,
>
>Can anybody please point me to Advantages and Disadvantages of using
> view
Sometimes, a view can fool you into writing hideously expensive queries
just because it is the first method that comes to mind.
I upgraded to version 8.2.4 a few days ago, and haven't gotten around to
change the memory settings. Thus, I just discovered that my pedigree
drawing script seemed to hang forever. I finally let it run its course,
and it clocked in on about two minutes. In a db where the largest table
is about 50,000 rows, this is ridiculous. I opened the script and
attacked the first query I found. It will find the children of the
focus person and list them ordered by birth date:
$query = "select person_id, pb_date from tmg_persons
where father_id = $p or mother_id = $p
order by pb_date";
tmg_persons is a view involving several function calls, and is a legacy
from an earlier, flatter data model where the 'persons' table actually
had this structure. I'm still using it in my Web application, and the
primary function of the view is to make an easy export:
CREATE OR REPLACE VIEW tmg_persons AS
SELECT
person_id,
get_parent(person_id,1) AS father_id,
get_parent(person_id,2) AS mother_id,
last_edit,
get_pbdate(person_id) AS pb_date,
get_pddate(person_id) AS pd_date,
gender AS s,
living AS l,
is_public AS p
FROM persons;
I ran an "explain select" on the query:
pgslekt=> explain select person_id, pb_date from tmg_persons where
father_id=1130;
QUERY PLAN
--------------------------------------------------------------------
Subquery Scan tmg_persons (cost=0.00..729.06 rows=81 width=36)
Filter: (father_id = 1130)
-> Seq Scan on persons (cost=0.00..525.96 rows=16248 width=19)
(3 rows)
Sequential scans usually spell Big Trouble. So, I rewrote the query to
read directly from the 'relations' table:
$query = "select child_fk, get_pbdate(child_fk) as pb_date
from relations
where parent_fk = $p
order by pb_date";
pgslekt=> explain select child_fk, get_pbdate(child_fk) as pb_date from
relations where parent_fk=1130 order by pb_date;
QUERY PLAN
---------------------------------------------------------------------------------
Sort (cost=150.52..150.81 rows=117 width=4)
Sort Key: get_pbdate(child_fk)
-> Bitmap Heap Scan on relations (cost=5.16..146.50 rows=117
width=4)
Recheck Cond: (parent_fk = 1130)
-> Bitmap Index Scan on parent_key (cost=0.00..5.13 rows=117
width=0)
Index Cond: (parent_fk = 1130)
(6 rows)
And that was it. The script now runs in about 1/10 of a second.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/
Ashish Karalkar wrote: > Hello All, > > Can anybody please point me to Advantages and Disadvantages of using view > > > With Regards > Ashish... Well, IMHO views are part of the "business logic" and not of the data model. You can also think of them as an API to access the data from applications (clients). By defining some nice views, you allow writing a client with little knowledge about the actual database design. And clients written by different people access the data consistently. However, this is a two-edged sword. An API is usually designed to be generic enough. One day you may find you just need only part of the funtionality, and you that could do that part more efficently. That's expecially true if the API is used to hide the details away from you. Normal clients may be given access only to the views and not to the actual tables. That's pretty an good design principle, but again it cuts both ways. Think of a database with a "books" table and a "authors" table, with a nice view that joins them. One day you are writing a client application and want to fetch just the list of book ids. Yes, you can select one column from the view, but why execute the join when you don't need it? But if you're given access only to the view, you can't do much about it. Of course this is not specific to views, it's true for any abstraction layer in any context. .TM.
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> [ this query got slow in 8.2: ]
> $query = "select person_id, pb_date from tmg_persons
> where father_id = $p or mother_id = $p
> order by pb_date";
> tmg_persons is a view involving several function calls, and is a legacy
> from an earlier, flatter data model where the 'persons' table actually
> had this structure. I'm still using it in my Web application, and the
> primary function of the view is to make an easy export:
> CREATE OR REPLACE VIEW tmg_persons AS
> SELECT
> person_id,
> get_parent(person_id,1) AS father_id,
> get_parent(person_id,2) AS mother_id,
> last_edit,
> get_pbdate(person_id) AS pb_date,
> get_pddate(person_id) AS pd_date,
> gender AS s,
> living AS l,
> is_public AS p
> FROM persons;
Are get_parent() and/or get_pbdate() marked volatile by any chance?
8.2 is more conservative about optimizing sub-selects involving volatile
functions than previous releases were, because we got complaints about
surprising behavior when a volatile function is executed more or fewer
times than the text of the query would suggest. If they are really
stable or immutable, marking them so would probably help here. (If they
fetch from another table, stable is the right marking.)
regards, tom lane
On Thursday 10. May 2007 19:23, Tom Lane wrote:
>"Leif B. Kristensen" <leif@solumslekt.org> writes:
>> CREATE OR REPLACE VIEW tmg_persons AS
>> SELECT
>> person_id,
>> get_parent(person_id,1) AS father_id,
>> get_parent(person_id,2) AS mother_id,
>> last_edit,
>> get_pbdate(person_id) AS pb_date,
>> get_pddate(person_id) AS pd_date,
>> gender AS s,
>> living AS l,
>> is_public AS p
>> FROM persons;
>
>Are get_parent() and/or get_pbdate() marked volatile by any chance?
>8.2 is more conservative about optimizing sub-selects involving
> volatile functions than previous releases were, because we got
> complaints about surprising behavior when a volatile function is
> executed more or fewer times than the text of the query would
> suggest. If they are really stable or immutable, marking them so
> would probably help here. (If they fetch from another table, stable
> is the right marking.)
Tom,
I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile'
yet, but rather reckoned that the default would do. Here are the
function definitions:
CREATE OR REPLACE FUNCTION get_parent(INTEGER,INTEGER) RETURNS INTEGER
AS $$
DECLARE
person ALIAS FOR $1; -- person ID
rel_type ALIAS FOR $2; -- gender code (1=male, 2=female)
par INTEGER; -- person ID of parent, returned by func
BEGIN
SELECT parent_fk INTO par FROM relations
WHERE child_fk = person AND relation_type = rel_type;
RETURN COALESCE(par,0); -- will return parent ID if it exists, 0
otherwise
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$
DECLARE
pb_date TEXT;
BEGIN
SELECT event_date INTO pb_date FROM events, participants
WHERE events.event_id = participants.event_fk
AND participants.person_fk = $1
AND events.tag_fk IN (2,62,1035)
AND participants.is_principal IS TRUE;
RETURN COALESCE(pb_date,'000000003000000001');
END;
$$ LANGUAGE plpgsql;
'relations', 'events', and 'participants' are actual tables. So, what do
you recommend?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> On Thursday 10. May 2007 19:23, Tom Lane wrote:
>> Are get_parent() and/or get_pbdate() marked volatile by any chance?
>> 8.2 is more conservative about optimizing sub-selects involving
>> volatile functions than previous releases were, because we got
>> complaints about surprising behavior when a volatile function is
>> executed more or fewer times than the text of the query would
>> suggest.
> I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile'
> yet, but rather reckoned that the default would do.
Yeah, I was against this particular change actually, because I expected
that it would cause more problems for people who hadn't paid close
attention to this point than it'd fix for those trying to do cute things.
> Here are the function definitions:
> CREATE OR REPLACE FUNCTION get_parent(INTEGER,INTEGER) RETURNS INTEGER
> AS $$
> DECLARE
> person ALIAS FOR $1; -- person ID
> rel_type ALIAS FOR $2; -- gender code (1=male, 2=female)
> par INTEGER; -- person ID of parent, returned by func
> BEGIN
> SELECT parent_fk INTO par FROM relations
> WHERE child_fk = person AND relation_type = rel_type;
> RETURN COALESCE(par,0); -- will return parent ID if it exists, 0
> otherwise
> END;
> $$ LANGUAGE plpgsql;
> CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$
> DECLARE
> pb_date TEXT;
> BEGIN
> SELECT event_date INTO pb_date FROM events, participants
> WHERE events.event_id = participants.event_fk
> AND participants.person_fk = $1
> AND events.tag_fk IN (2,62,1035)
> AND participants.is_principal IS TRUE;
> RETURN COALESCE(pb_date,'000000003000000001');
> END;
> $$ LANGUAGE plpgsql;
AFAICS you ought to mark both of those STABLE, since they use but don't
change database data.
regards, tom lane
On Thursday 10. May 2007 21:21, Tom Lane wrote: >"Leif B. Kristensen" <leif@solumslekt.org> writes: >> I haven't pondered the subtleties of 'stable', 'immutable' or >> 'volatile' yet, but rather reckoned that the default would do. > >Yeah, I was against this particular change actually, because I > expected that it would cause more problems for people who hadn't paid > close attention to this point than it'd fix for those trying to do > cute things. > >> Here are the function definitions: >AFAICS you ought to mark both of those STABLE, since they use but > don't change database data. Tom, thanks for your explanation. As always, it is lucid and to the point. Would it be reasonable to suggest that later versions of PostgreSQL could examine if a function changes data, and quietly marks a function as 'stable' if it doesn't? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/
Michael Glaesemann <grzm@seespotcode.net> writes:
> Two people now have stated without much qualification that views have
> some kind of associated performance (Brent Woods) or optimization
> (Dann Corbit) penalty. Where does this idea come from? Views in
> PostgreSQL are just rewritten with the view query inlined! There's
> not much overhead there AIUI.
Well, it takes some cycles to rewrite the query with the inserted
sub-select, but probably fewer than would be taken to parse and analyze
the query if it had been written out longhand (the stored form of the
view has already gone through parse analysis, so we don't have to repeat
that work for it). AFAIK that's at worst a wash. I suspect the
important point here is that if you have
CREATE VIEW v AS SELECT sis, boom, bah ...
then
SELECT ... FROM ..., v, ...
will be rewritten to the same parsetree as if you'd written
SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ...
and then everything hinges on what the planner is able to do with that.
In simple cases the planner is able to "flatten" the sub-SELECT together
with the outer query and you get a reasonable plan, but if it fails to
do that then you might get a pretty bad plan. I think some people might
complain that "views are slow" because they compared the view to a case
that is not exactly the above mechanical transformation, but one where
they had applied some simplification/optimization that was obvious to
them but not to the planner.
regards, tom lane
On Thu, 10 May 2007 00:06:06 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > ... I suspect the > important point here is that if you have > > CREATE VIEW v AS SELECT sis, boom, bah ... > > then > > SELECT ... FROM ..., v, ... > > will be rewritten to the same parsetree as if you'd written > > SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ... > > and then everything hinges on what the planner is able to do with that. > In simple cases the planner is able to "flatten" the sub-SELECT together > with the outer query and you get a reasonable plan, but if it fails to > do that then you might get a pretty bad plan. I think some people might > complain that "views are slow" because they compared the view to a case > that is not exactly the above mechanical transformation, but one where > they had applied some simplification/optimization that was obvious to > them but not to the planner. I think I have a classic example of this (for older pg versions anyway) - we have a lot of views with a left join in them and performance is awful when the view is inner joined to another table. "select v.* from v where key_of_1st_table = blah" takes a small fraction of a second. "select v.* from v join analysed_tmp_containing_only_blah using (key_of_1st_table)" takes a coffee and a doughnut. The outer join reordering in 8.2 should solve this situation though? klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> Would it be reasonable to suggest that later versions of PostgreSQL
> could examine if a function changes data, and quietly marks a function
> as 'stable' if it doesn't?
My instinctive CS-major reply to that is "only if you've found a
solution to the halting problem". However, it's possible that we could
detect this case for a useful subset of real-world functions ... not
sure offhand what could be covered.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/10/07 21:24, Tom Lane wrote: > "Leif B. Kristensen" <leif@solumslekt.org> writes: >> Would it be reasonable to suggest that later versions of PostgreSQL >> could examine if a function changes data, and quietly marks a function >> as 'stable' if it doesn't? > > My instinctive CS-major reply to that is "only if you've found a > solution to the halting problem". However, it's possible that we could > detect this case for a useful subset of real-world functions ... not > sure offhand what could be covered. If there are no INSERT, UPDATE or DELETE statements in the function? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQ9nCS9HxQb37XmcRAuxyAJ9Setk7j5/xg5jwvNi3o6RDceuGLACg1FDS LptxOrJvoNVSjEATWIeFo+Y= =5MT8 -----END PGP SIGNATURE-----
Ron Johnson <ron.l.johnson@cox.net> writes:
> On 05/10/07 21:24, Tom Lane wrote:
>> My instinctive CS-major reply to that is "only if you've found a
>> solution to the halting problem". However, it's possible that we could
>> detect this case for a useful subset of real-world functions ... not
>> sure offhand what could be covered.
> If there are no INSERT, UPDATE or DELETE statements in the function?
Nor any function calls ... which leaves about nothing ...
regards, tom lane
Ron Johnson <ron.l.johnson@cox.net> writes: > On 05/10/07 21:24, Tom Lane wrote: >> "Leif B. Kristensen" <leif@solumslekt.org> writes: >>> Would it be reasonable to suggest that later versions of PostgreSQL >>> could examine if a function changes data, and quietly marks a function >>> as 'stable' if it doesn't? >> >> My instinctive CS-major reply to that is "only if you've found a >> solution to the halting problem". However, it's possible that we could >> detect this case for a useful subset of real-world functions ... not >> sure offhand what could be covered. > > If there are no INSERT, UPDATE or DELETE statements in the function? And all functions called from inside the one being run as well (recursive condition, of course)... -- Jorge Godoy <jgodoy@gmail.com>
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/10/07 22:21, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> On 05/10/07 21:24, Tom Lane wrote: >>> My instinctive CS-major reply to that is "only if you've found a >>> solution to the halting problem". However, it's possible that we could >>> detect this case for a useful subset of real-world functions ... not >>> sure offhand what could be covered. > >> If there are no INSERT, UPDATE or DELETE statements in the function? > > Nor any function calls ... which leaves about nothing ... I figured that might be the sticky wicket. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQ+wqS9HxQb37XmcRAt25AJ9mt9IkQjCJBV3EySDRyvzE5bcu/wCeOAiv ntHA65FcBMU3dmLsP1ZD4lE= =sbtA -----END PGP SIGNATURE-----
On Fri, 11 May 2007 04:24:55 +0200, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Leif B. Kristensen" <leif@solumslekt.org> writes:
>> Would it be reasonable to suggest that later versions of PostgreSQL
>> could examine if a function changes data, and quietly marks a function
>> as 'stable' if it doesn't?
>
> My instinctive CS-major reply to that is "only if you've found a
> solution to the halting problem". However, it's possible that we could
> detect this case for a useful subset of real-world functions ... not
> sure offhand what could be covered.
>
> regards, tom lane
Why not simply have PG issue a warning if the user doesn't specify one of
("stable", "immutable", etc) on function creation ?
like :
WARNING: Function marked as Volatile by default
INFO: if the function does not modify the database, you might want to mark
it STABLE or IMMUTABLE to improve performance
Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> On 05/10/07 21:24, Tom Lane wrote: >>> My instinctive CS-major reply to that is "only if you've found a >>> solution to the halting problem". However, it's possible that we could >>> detect this case for a useful subset of real-world functions ... not >>> sure offhand what could be covered. > >> If there are no INSERT, UPDATE or DELETE statements in the function? And no EXECUTE or PERFORM statements probably, or you'd have to determine if the succeeding string might evaluate to INSERT, UPDATE or DELETE. And of course DDL statements would be out of the question too... > Nor any function calls ... which leaves about nothing ... Wouldn't that only be the case if those were volatile? -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //