Re: Query Rewrite for Materialized Views (Postgres Extension)

2018-06-16 16:21 GMT+02:00 John Dent <denty(at)qqdd(dot)eu>:

> 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.
>

good idea.

Regards

Pavel

> 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.
>
>
>