[GENERAL] Is there any way to index or cache a view, or function results?
Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006
Re: [GENERAL] Is there any way to index or cache a view, or function results?
I was hoping for something a bit more automatic with less maintenance from me. Thank you for your reply. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -Original Message- From: Tomi NA [mailto:[EMAIL PROTECTED] Sent: Saturday, July 01, 2006 4:17 PM To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Is there any way to index or cache a view, or function results? On 7/1/06, Jason Long [EMAIL PROTECTED] wrote: Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? You could create a new table to store the results in and refresh it's contents every time the original data changes and you can index whatever you want, as long as you take into account that frequent and extensive changes to a table with a couple of indexes might slow things down a bit, depending on the number of changed records, the number of indices you define on the table and so on... You can keep the data in sync using triggers or with a periodic update task, depending on what kind of precision you need. Cheers, t.n.a. -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 ---(end of broadcast)--- TIP 1: 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] Is there any way to index or cache a view, or function results?
I was hoping for something a bit more automatic with less maintenance from me. Thank you for your reply. On 7/1/06, Jason Long [EMAIL PROTECTED] wrote: Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? You could create a new table to store the results in and refresh it's contents every time the original data changes and you can index whatever you want, as long as you take into account that frequent and extensive changes to a table with a couple of indexes might slow things down a bit, depending on the number of changed records, the number of indices you define on the table and so on... You can keep the data in sync using triggers or with a periodic update task, depending on what kind of precision you need. Perhaps an implemenation of a materialized view is more what you would like? http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Regards, Richard Broersma jr. ---(end of broadcast)--- TIP 1: 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] Is there any way to index or cache a view, or function results?
Thanks that is basically what I was looking for I will investigate further. I appreciate your response. Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.supernovasoftware.com HJBUG Founder and President http://www.hjbug.com -Original Message- From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] Sent: Saturday, July 01, 2006 4:49 PM To: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there any way to index or cache a view, or function results? I was hoping for something a bit more automatic with less maintenance from me. Thank you for your reply. On 7/1/06, Jason Long [EMAIL PROTECTED] wrote: Is it possible to define a function or view that performs fairly intensive calculations and then index or cache these results? The data I have will be accessed more than modified, but still will be modified semi regularly. Would someone please enlighten me on my options for improving performance is this situation? You could create a new table to store the results in and refresh it's contents every time the original data changes and you can index whatever you want, as long as you take into account that frequent and extensive changes to a table with a couple of indexes might slow things down a bit, depending on the number of changed records, the number of indices you define on the table and so on... You can keep the data in sync using triggers or with a periodic update task, depending on what kind of precision you need. Perhaps an implemenation of a materialized view is more what you would like? http://archives.postgresql.org/pgsql-performance/2004-02/msg00279.php http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Regards, Richard Broersma jr. -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq