Re: Postgresql Materialized views - Mailing list pgsql-hackers
From | Mark Mielke |
---|---|
Subject | Re: Postgresql Materialized views |
Date | |
Msg-id | 478B5F78.6030508@mark.mielke.cc Whole thread Raw |
In response to | Re: Postgresql Materialized views (Jean-Michel Pouré <jm@poure.com>) |
Responses |
Re: Postgresql Materialized views
(Alvaro Herrera <alvherre@commandprompt.com>)
|
List | pgsql-hackers |
Jean-Michel Pouré wrote: > When posting this thread, I hope that a talented developer would some > day pick-up the materialized view issue and work on it, during a process > of discussion. > In case it was lost in the noise - there are several capable people that have been personally on this issue for years. You are not the first to suggest it, and your opinion that the feature is valuable is shared by a larger group. There is still a difference between talking about it and doing it. A few of the doers told me off privately stating that it is not that difficult. My suspicion is that it *is* difficult and they are not doing themselves credit, or their solution is incomplete, but whatever - the result is the same. When one or more of these people are ready, you will likely see it released. It may even be complete before 2008 is complete. > In a few days, I will post some precise statistics on how much > MATERIALIZED VIEW could boost a 400.000 posts PhpBB 3.0 forum. I will > write the required PL trigger, test them and install them on my server. > The two factors of note here: 1) If you can write a PL trigger for it, the value of the feature is limited. In my own case, I found it fairly easy to write triggers, or update the summary table from the application (poor man's view). 2) In my experience, a custom PL trigger can make assumptions about the application that allow greater optimization that a general 'syntactical sugar' solution could. In my own case, performance of queries leapt from 1500 ms to 1 ms. Even if materialized views were implemented to a level that most people would consider "full", I do not expect to see the same speed improvement, because a generalized implementation would not be able to make the assumptions that I can. FYI, my triggers are perhaps 10 lines each, and I believe I have three triggers in the 1500 ms -> 1 ms example. I have a view and a summary table. I update the summary table from the view. In my opinion, this solution is very manageable given the 1500:1 performance improvement it grants me. > Then each individual is able to decide whether materiazed views are > important or not. This is my definition of freedom. Freedom of choice. > > I don't ask for more. > Now, I hope that the list can return to a more peaceful state You are doing fine. I am sorry for assuming you intended more and giving you a cold-ish shoulder. Cheers, mark -- Mark Mielke <mark@mielke.cc>
pgsql-hackers by date: