Query Rewrite for Materialized Views (Postgres Extension)

Hi folks,

I thought I’d share an update to my pet project, which dynamically rewrites queries to target materialized views when they are available and can satisfy a query (or part of it) with a lower cost plan.

The extension is now a regular EXTENSION and no longer tied in to the FDW mechanism. As a result, it may now be more generally usable, and less complicated to integrate into an existing system. (The FDW approach was an easy way for me to get started, but it ultimately added complexity and was rather limiting.)

Same caution as before applies:

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

Built, and has rudimentary testing against Postgres 10.1..10.3.

Source code: https://github.com/d-e-n-t-y/pg_fdw_mv_rewrite
README: https://github.com/d-e-n-t-y/pg_fdw_mv_rewrite/blob/master/README.md

Hope it is useful or interesting for someone! Questions or comments are very welcome.

denty.

> Begin original message:
>
> From: Dent John <denty(at)QQdd(dot)eu>
> Subject: Query Rewrite for Materialized Views (FDW Extension)
> Date: 5 April 2018 at 14:41:15 BST
> To: pgsql-hackers(at)lists(dot)postgresql(dot)org
>
> Hi,
>
> I wanted to share the project I've been working on which dynamically rewrites queries to target materialized views when views are available that can satisfy part of a query with lower cost plans.
>
> I embarked upon as an interesting side project. It took me a bit more time than I anticipated, but the result works for my use case. Because of that, I thought it worth sharing. However I would caution that my use case is not exactly of a commercial scale... so please heed the following obligatory warning:
>
> **NOTE: this is not "production ready" code — if it works for you, then great, but use it after thorough testing, and with appropriate caution.**
>
> There are some limitations to the rewrite opportunities it takes up, and it will almost certainly fail on complex materialized views composed of deeply nested queries.
>
> The extension does not have extensive (actually: any) documentation, but the few test cases should make obvious to the inclined reader how it works. This is deliberate at this early a stage: I don't want to encourage uninformed adoption because of the possibility of data loss or incorrect query rewrites.
>
> The extension is written against a Postgres 10.1 source tree.
>
> Source code: https://github.com/d-e-n-t-y/pg_fdw_mv_rewrite
>
> Questions or comments are very welcome.
>
> denty.