This is a query rewriting extension for PostgreSQL that transparently rewrites queries to access MATERIALIZED VIEWs, where use of that MATERIALIZED VIEW would result in a lower cost plan.

NOTE: this is not "production ready" code — if it works for you, then great, but use it after thorough testing, and with appropriate caution.

To get started, load the mv_rewrite extension.

First load the EXTENSION:

postgres=# create extension mv_rewrite;
CREATE EXTENSION

Query rewrite can happen transparently. However, to understand what is going on, it can be helpful to enable some basic feedback.

Enable progress logging during the matching process:

postgres=# set mv_rewrite.log_match_progress = 'true';
SET

Given a simple TABLE and data, for example:

postgres=# create table test (key text, hidden text, value text);
CREATE TABLE
postgres=# insert into test (select 'key1', 'hidden1', generate_series (1, 10));
INSERT 0 10
postgres=# insert into test (select 'key2', 'hidden2', generate_series (1, 1e6));
INSERT 0 1000000

And a supporting MATERIALIZED VIEW, for example:

postgres=# create materialized view test_mv1 as select key, count(value) FROM test GROUP BY key;
SELECT 2

A simple GROUP BY aggregate query against test results in a plan of some 10k, for example:

postgres=# explain select key, COUNT (value) from test group by key;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=13620.28..13620.31 rows=1 width=13)
   Group Key: key
...

If test and its MATERIALIZED VIEW (test_mv1) are added for rewrite as follows:

postgres=# select mv_rewrite.enable_rewrite ('test_mv1');
 enable_rewrite 
----------------
 
(1 row)

Now aggregate queries targetting test will consider if they can be satisfied instead by test_mv1, as demonstrated in the following query plan:

postgres=# explain (verbose) select key, COUNT (value) from test group by key;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (MVRewriteScan)  (cost=0.00..22.00 rows=1200 width=13)
   Output: key, (count(value))
   Rewritten: scan of public.test_mv1
   Original costs: cost=13620.28..13620.31 rows=1 width=13; cost=21370.15..21370.16 rows=1 width=13; cost=133119.51..140619.60 rows=1 width=13

Please heed the warning about this not being production ready!

Query rewrite is only considered for:

• queries involving GROUP BY aggregates;
• queries involving DISTINCT (but not DISTINCT ON);
• queries that are ORDERed;
• simple SELECTS and queries that involve JOINs;
• queries with WHERE and/or HAVING clauses.

RECURSIVE queries are not yet properly supported, nor are LATERAL joins.

Rewriting cam be a costly planning operation, and it may be undesirable to apply the query rewriting search for plans that are already relatively cheap. A threshold can be set beneath which rewrite will not be attempted.

For example:

postgres=# set mv_rewrite.rewrite_minimum_cost = '1000000.0';
SET

A minimum cost of 1,000,000 means that only relatively costly queries will considered for rewriting.

The default (-1) causes rewrite to be considered for all supported queries.

If you have mv_rewrite.log_match_progress enabled, then the fact of rewriting not being considered for reason of already having a plan of acceptable cost in hand is announced as follows:

INFO:  mv_rewrite_create_upper_paths_hook: already have path with acceptable cost.

It may be desirable that rewrite is applied only to a certain set of large tables, or to tables that are typically subjected to complex queries. This limitation can be configured by by setting mv_rewrite.rewrite_enabled_for_tables.

SETting mv_rewrite.rewrite_enabled_for_tables to DEFAULT results in rewrite being considered for all tables, whereas an empty string means it is effectively disabled.

set mv_rewrite.rewrite_enabled_for_tables = 'public.table1,public.table2';

If you have mv_rewrite.log_match_progress enabled, then the fact of rewriting not being considered for reason of the query targetting tables not enabled for rewrite is announced as follows:

INFO:  mv_rewrite_add_rewritten_mv_paths: MV rewrite not enabled for one or more table in the query.

It can be useful to disable rewrite, especially during an MV refresh operation.

For example:

postgres=# set mv_rewrite.rewrite_enabled = false;
SET