Re: Postgresql Materialized views

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>