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(at)mielke(dot)cc>