Materialized Views Performance Tips

Let's go over two of the kinds of materialized views you might have:
Write-Once, and Maintain. Both start out the same way.

DROP TABLE IF EXISTS mview;

CREATE TABLE mview AS SELECT * FROM my_view;

[CREATE INDEX mview_idx_foo ON mview(...) ; CREATE INDEX ... ; ... ;]

DROP VIEW my_view;

With the Write-Once view, your work is now done.

With Maintain, the work continues, for example by TRIGGER or batch processing.
In the Maintain case, you'd like your maintenance operations to touch as
little of the database as practical, as table- and full-result scans will only
grow over time.

Let's take an example of a VIEW that gets increasingly burdensome over time
when not maintained correctly:

SELECT a, avg(b) FROM c GROUP BY a;

Rather than storing avg(b) directly, which would require a full-table scan
each time, break it into pieces you can treat at a less-than-whole-scan scale,
namely count(b) and sum(b). Given that you're storing statistics, you might
want to store sum(b^2) for calculating things like standard deviations, and
maybe even sum(b^3) if you're interested in something exotic like skew, so
your original query would look like:

    CREATE TABLE intermediate_mview AS

    SELECT

        foo,

        count(*)         AS count,

        sum(bar)         AS sum_bar,

        sum(bar*bar)     AS sum_bar_squared, 

        sum(bar*bar*bar) AS sum_bar_cubed

    FROM quux

    GROUP BY foo;

Your maintenance routines--simple ones would be TRIGGERs--only need to look at
changed rows in order to maintain that view. An INSERT into c with a
particular value of foo would lead to updates on count, sum_bar, etc. without
looking at other rows in quux. Similar rules apply to DELETE and UPDATE
operations, the latter being logically equivalent to DELETE + INSERT.

If you TRUNCATE quux, make sure you do the same with your intermediate_mview.