Fast Filtering for Materialized Views when Answering Query.

[avamingli]

Description

The AQUMV(Answer Query Using Materialized Views) enable us to rewrite queries to select from materialized views/dynamic tables and incremental materialized views.

In scenarios where materialized views are implemented, the query planner must scan through many potential candidates to determine if they can be used to answer the original queries. This process becomes increasingly time-consuming as the number of materialized views grows.

The paper Optimizing Queries Using Materialized Views:A Practical, Scalable Solution discusses how as the number of views increases, the efficiency of query optimization can deteriorate. Specifically, the time taken for query processing can rise significantly, leading to diminishing returns on the benefits that materialized views are supposed to provide.

Efficiently filtering materialized views (MVs) is crucial for optimizing query performance.
As highlighted in the "Fast Filtering of Views" section of the paper:

With 1000 views, the optimization time increases by about 110% when the filter tree is disabled. The pager introduced a Lattice index filter tree to fetch the views.

Recently, we often hear complaints about increased planning time when using AQUMV in scenarios with hundreds of materialized views on a table. Fast filtering for materialized views is essential, and now it’s time to implement it.

Our goal is to design a fast filtering index method inspired by the techniques discussed in the paper. The approach may differ significantly based on the PostgreSQL planner . This index could efficiently narrow down candidate materialized views based on their compatibility with incoming queries, thus minimizing the time spent in the planner.

One immediate area for optimization is that searching for materialized views requires scanning the pg_rewrite table, where the SQL corresponding to the views is stored. PostgreSQL has around 200 built-in rules, which are not user-created for the AQUMV functionality. Even without user-created materialized views, if enable_answer_query_using_materialized_views is turned on, it still requires traversing all pg_rewrite data. Here is an example:

gpadmin=# set enable_answer_query_using_materialized_views = off;
SET
gpadmin=# explain(analyze, costs off, verbose) select count(a) from t_select;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
-
 Finalize Aggregate (actual time=0.000..0.000 rows=1 loops=1)
   Output: count(a)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual time=0.000..0.000 rows=3 loops=1)
         Output: (PARTIAL count(a))
         ->  Partial Aggregate (actual time=0.000..0.000 rows=1 loops=1)
               Output: PARTIAL count(a)
               ->  Seq Scan on public.t_select (actual time=0.000..0.000 rows=340 loops=1)
                     Output: a
 Settings: enable_answer_query_using_materialized_views = 'off', optimizer = 'off'
 Planning Time: 0.462 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 112K bytes avg x 3x(0) workers, 112K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1.898 ms
(15 rows)

With enable_answer_query_using_materialized_views = off, the planning time is 0.462 ms.

gpadmin=# set enable_answer_query_using_materialized_views = on;
SET
gpadmin=# explain(analyze, costs off, verbose) select count(a) from t_select;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3) (actual time=0.000..0.000 rows=1 loops=1)
   Output: count
   ->  Seq Scan on public.mv_insert_select (actual time=0.000..0.000 rows=1 loops=1)
         Output: count
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Planning Time: 78.942 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 114K bytes avg x 3x(0) workers, 114K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1.695 ms
(11 rows)

With enable_answer_query_using_materialized_views = on, the planning time increases to 78.942 ms.

As an initial step in the overall plan, this part could be optimized by utilizing other system tables, such as gp_matview_aux, to skip unnecessary scans.

Further details on fast filtering for materialized views will be added.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[avamingli]

As an initial step in the overall plan, this part could be optimized by utilizing other system tables, such as gp_matview_aux, to skip unnecessary scans.

#1117

[avamingli]

part2 #1138