Re: [GENERAL] Performance of views
Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: Note that, at least in older versions, MySQL completely materialized a temporary table from a view, then used that for the view. This is horribly inefficient, and results in a lot of people thinking views are slow. Not sure if this has been addressed in MySQL yet, don't really care anymore, since I rarely use mysql for anything anymore. Some simple experiments with mysql 5.0.67 suggest that this meme is obsolete there too. I found some cases where it looks like we optimize a bit better than they do, but for simple views you seem to get the same plan as if you'd written out the equivalent query in-line. This is true of simple queries, but as soon as your query starts becoming complex (e.g. involving subselects) it just dumps the result of the view query into a temporary table and uses that. (Tested with 5.0.67 too). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance of views
Hi Generally, I have avoided using VIEWS within application code and only used them for client interfaces, the sole reason being the performance of views against tables. Changes to database over the past few years appear to have improved the performance of views, but I am still not comfortable with using VIEWS within application code. The main reasons I have are * Data within a view is not necessary sequential, unlike a table * Higher overhead mapping to original tables and indexes * Danger of linking views and tables and not utilising utilising underlying tables properly. Am I right to avoid to VIEWS within application code? Simon -- Simon Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
On Sun, Nov 2, 2008 at 4:11 PM, Simon Windsor [EMAIL PROTECTED] wrote: Hi Generally, I have avoided using VIEWS within application code and only used them for client interfaces, the sole reason being the performance of views against tables. Have you confirmed this belief with tests? Or just following some kind of general knowledge that's filtered down from other dbas? Changes to database over the past few years appear to have improved the performance of views, but I am still not comfortable with using VIEWS within application code. The main reasons I have are This is true for some other dbms'. It's generally not true for PostgreSQL. PostgreSQL views have for a very long history of running as queries the same as if you ran them by hand. This is due to the use of an advanced rules system that allows for the same performance of views as for the original queries on the table. * Data within a view is not necessary sequential, unlike a table Data in a pgsql view is EXACTLY the same as if you ran the query by hand. * Higher overhead mapping to original tables and indexes The overhead in pgsql is measured in sub millisecond time. * Danger of linking views and tables and not utilising utilising underlying tables properly. not sure what you mean there at all. Am I right to avoid to VIEWS within application code? Yes. Absolutely. If you're running MySQL. OTOH, if you're in pgsql there's no real reason to avoid them. unless you'll also be supporting mysql. then mysql plays the lowest common denominator trump card it so often does and forces you to dumb down your db layer to make it happy. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
Am I right to avoid to VIEWS within application code? How one uses views is more a matter of taste and best practices, than a matter of rules like this. Frankly, this rule sounds rather ill conceived. My feeling is that views can be difficult to maintain when they are nested, but otherwise use them whenever they simplify matters. I also feel that they should only be defined for when they represent, well, views of the data that make sense in a long term way; don't use them if for a one-off application. As for performance -- let me reiterate: create the most elegant design, possibly with views, and only worry about performance AFTER PROFILING. -W -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
Simon, * Simon Windsor ([EMAIL PROTECTED]) wrote: Generally, I have avoided using VIEWS within application code and only used them for client interfaces, the sole reason being the performance of views against tables. Views really shouldn't have a large impact on overall performance. In PostgreSQL, that's even more true. In general, I would strongly recommend moving complex queries from your application into views in the database. The performance difference really should be minimal, while the maintainability is improved. * Data within a view is not necessary sequential, unlike a table I've got no idea what you're talking about here, to be honest. Pulling data out of a table has no guarenteed ordering to it unless you explicitly ask for one, and you can do that in a view too. * Higher overhead mapping to original tables and indexes This just plain isn't true in PG, at least, and I'd think most other sensible databases.. * Danger of linking views and tables and not utilising utilising underlying tables properly. If you push your complicated queries into your views and keep the application code simpler, identifying and fixing performance or other issues often becomes easier, and easier to fix.. Am I right to avoid to VIEWS within application code? No. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Performance of views
On Sun, Nov 2, 2008 at 6:39 PM, Stephen Frost [EMAIL PROTECTED] wrote: Simon, * Higher overhead mapping to original tables and indexes This just plain isn't true in PG, at least, and I'd think most other sensible databases.. Note that, at least in older versions, MySQL completely materialized a temporary table from a view, then used that for the view. This is horribly inefficient, and results in a lot of people thinking views are slow. Not sure if this has been addressed in MySQL yet, don't really care anymore, since I rarely use mysql for anything anymore. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
Scott Marlowe [EMAIL PROTECTED] writes: Note that, at least in older versions, MySQL completely materialized a temporary table from a view, then used that for the view. This is horribly inefficient, and results in a lot of people thinking views are slow. Not sure if this has been addressed in MySQL yet, don't really care anymore, since I rarely use mysql for anything anymore. Some simple experiments with mysql 5.0.67 suggest that this meme is obsolete there too. I found some cases where it looks like we optimize a bit better than they do, but for simple views you seem to get the same plan as if you'd written out the equivalent query in-line. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
We've been toying around with reworking our years old database schema and replacing the old tables with updatable views into the new schema. The only real problem we've had with it is that queries to one of our views seem to be joining on unnecessary tables because the view does the join. We don't need the columns provided by the join and the join is kind of costly, but performance has been great otherwise. On Sun, Nov 2, 2008 at 8:59 PM, Scott Marlowe [EMAIL PROTECTED]wrote: On Sun, Nov 2, 2008 at 6:39 PM, Stephen Frost [EMAIL PROTECTED] wrote: Simon, * Higher overhead mapping to original tables and indexes This just plain isn't true in PG, at least, and I'd think most other sensible databases.. Note that, at least in older versions, MySQL completely materialized a temporary table from a view, then used that for the view. This is horribly inefficient, and results in a lot of people thinking views are slow. Not sure if this has been addressed in MySQL yet, don't really care anymore, since I rarely use mysql for anything anymore. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of views
does anyone know if postgres support 'refresh' of applicable index(es) of a materialized view on refresh? Thanks, Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [GENERAL] Performance of views Date: Sun, 2 Nov 2008 21:22:24 -0500 From: [EMAIL PROTECTED] Scott Marlowe [EMAIL PROTECTED] writes: Note that, at least in older versions, MySQL completely materialized a temporary table from a view, then used that for the view. This is horribly inefficient, and results in a lot of people thinking views are slow. Not sure if this has been addressed in MySQL yet, don't really care anymore, since I rarely use mysql for anything anymore. Some simple experiments with mysql 5.0.67 suggest that this meme is obsolete there too. I found some cases where it looks like we optimize a bit better than they do, but for simple views you seem to get the same plan as if you'd written out the equivalent query in-line. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ When your life is on the go—take your life with you. http://clk.atdmt.com/MRT/go/115298558/direct/01/
Re: [GENERAL] Performance of views
On Sun, Nov 2, 2008 at 7:40 PM, Martin Gainty [EMAIL PROTECTED] wrote: does anyone know if postgres support 'refresh' of applicable index(es) of a materialized view on refresh? Postgresql has no built in support for materialized views. If you follow the excellent tutorial on how to make your own, you window up creating real tables to hold the data, and any indexes you create on those tables will be dynamically updated when the materialized view is updated. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance of Views
Hello there, i have a short question ... I have a few tables (at the moment only 3 for testing), over which will by made a query in this form: SELECT a.orderitem_id, a.transaction_id, a.order_id, a.shop_id, a.quantity, a.price, b.affiliate_id, c.type FROM ss_order_orderitems a LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id, ss_shops c WHERE (a.order_id = b.order_id OR b.order_id IS NULL) AND a.shop_id = c.shop_id; The query will get later a few more conditions ... Now is the problem, that the performance is not realy good ... and i think about the best solution for such a query and found three possibilitys: 1. a simple select over the three tables (one of them contains 16 entrys in the moment and it's growing) in the form like above (the db is mysql 4.1.x) I think this solution is not very perfomant ... 2. move the data to a postgresql-db with the same structur and create a view, wich makes the same query ... Is the performance for the same query different between a simple select and a view!? If so, i can forget the view ... 3. put the data with the above query in one big table ... I know, thats no good db-structur, but i don't know how i could make it better ... The main-question at the moment iss ... Is the performance of the View-Method better then the first Method on the existing tables!? I hope of a view hints ... Thanks Steffen ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Performance of Views
Steffen Boehme [EMAIL PROTECTED] writes: FROM ss_order_orderitems a LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id, ss_shops c WHERE (a.order_id = b.order_id OR b.order_id IS NULL) AND What is that last line doing there? It's completely redundant and could very well be the source of your problems. For useful help you should post the \d output for the three tables and the result of EXPLAIN ANALYZE SELECT The main-question at the moment iss ... Is the performance of the View-Method better then the first Method on the existing tables!? A view doesn't change performance at all. It's exactly the same as writing the query in the view directly into your query. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
