Re: Query Rewrite for Materialized Views (FDW Extension)

Hi John,

Can you characterize the class of queries that the MVs eligible for
query rewrite may include, and the class of statements that may be rewritten
using those mvs, in terms of joins and join types, aggregation, constraints,
types of aggregate operators, and allowed aggregate expressions?

For example:
- the mv may contain any number of joins, which may be either inner
or left outer, semi-, or anti joins
- aggregates are optional, but if present, all columns in aggregate
expressions must refer to the same table
- MIN, MAX, SUM, COUNT, AVG aggregate operations are allowed
- to be eligible for query rewrite a user must ... <fill in the
details>
- local predicates are / not allowed in the MV

- the statement being rewritten may have one or more joins.
- the joins need not be identical to the corresponding joins in the
MV, but <fill in the details>
- there may be joins in the mv that are not in the statement
provided that ... <fill in the details>
- any restrictions or assumed dependence on fdw's, since the name
'fdw extension' implies as much
- MV rewrite will / not be cost-based
- The statement may / not have local predicates that are not
identical to the local predicates in the mv

If you're going to do MV rewrite using MVs that aren't transactionally
consistent with the underlying tables, then you're going to need a different
sort of isolation mode, or some sort of permissions model that enables users
to opt-in to permit the optimizer to give back "wrong results" from
potentially stale MVs.

I didn't attend the Ottawa conference this year, so I'd be interested to
hear a summary of what the community thinks about MV incremental maintenance
as well. There are several important subtypes of MV incremental
maintenance:

1) incremental refresh on-commit, for individual row DML (enables MVs
to be transactionally consistent, but incurs overhead at COMMIT time)
2) bulk incremental refresh on commit or on demand, after a COPY
operation (more for a DW use case, but much more efficient for bulk-insert
scenarios, and permits some optimizations that are not possible for the
first case)
3) full refresh by partition (not an automatic solution, and there are
some subtleties here about rows that move from one partition to another, but
it's an easy first step toward incremental mv maintenance without most of
the complexity)

there is also the possibility of doing mv refresh from other mv's that have
already been refreshed, so mv rewrite and mv refresh sometimes interact.

thank you,

/Jim F

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html