Merlin Moncure wrote:
On Jan 12, 2008 4:19 PM, Simon Riggs <[EMAIL PROTECTED]> wrote:
On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
Please pick-up this important issue for developpers. There is no need to
concentrate on complex issues, when handling materialized views could
boost somme web apps. by a factor of 10 or more.
It's more complex than you think, but the main reason was that HOT was a
prerequisite for making summary tables work efficiently, which is only
now just about to go live into 8.3
+1
I don't quite agree with that. HOT certainly speeds up UPDATEs on small
tables, like you a summary table, but there's a lot of use cases like
data warehousing, where the summary tables are not updated that often
for the updates to become a bottleneck.
If you know how to write triggers, materialization techniques aren't
all that difficult. The real technical limitation was not lack of
materialization techniques (write triggers), but was dealing with the
mvcc penalty. Previously to HOT, for summary tables I would redirect
the trigger to insert to a 'roll up' table and move the data to the
summary on cron or from an application event.
Materialized views are syntax sugar (but still very sweet).
There's two things involved in materialized views:
1. Automatically updating the materialized view, when the tables change.
This can be done with triggers, right now, but requires quite a bit of
manual work to set up, especially with more complex views.
2. Using the materialized views to speed up existing queries. For
example, if you have a materialized view on "SELECT COUNT(*) FROM foo",
and someone issues the query "SELECT COUNT(*) FROM foo", the planner
should automatically use the view to satisfy that.
1 is syntactic sugar, but 2 isn't.
These are orthogonal features. Implementing just 1 without 2 would still
be very useful, and in fact that seems to be what most people mean by
materialized views.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
