Re: Can I use PostgreSQL to develop a self-organizing

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:

> On Mon, Sep 08, 2003 at 05:55:43PM -0400, Greg Stark wrote:
>
> > Materialized views are a godsend for DSS systems, and self-organized
> > materialized views sound too good to be true. Is this really feasible?
> > It sounds awfully ambitious considering postgres doesn't even have
> > materialized views yet.
>
> There is also the question of how would one actually use self-organizing
> views... what would be the syntax for them? Should the parser recognize
> that some join you are giving to it is a previously self-organized view
> and contract the sentence?

In an ideal world the database would just magically notice that I do a lot of
queries like: "SELECT count(*) FROM foo WHERE bar = ?"
and decide to materialize: "SELECT count(*),bar FROM foo GROUP BY bar"
(and figure out where to put the index.)

Then it would optimize all my queries to be a simple indexed lookup.

Of course it would also have to add rewrite rules or triggers to update the
materialized view on every update/insert/delete. So the code to decide when to
create the materialized view would have to keep statistics on these queries as
well to decide when it's worthwhile to create the materialized view... Nobody
said it was easy :)

--
greg