Skip to content

[AQUMV] Store view query in gp_matview_aux for view matching. #1117

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
my-ship-it merged 1 commit into from
May 23, 2025
Merged

[AQUMV] Store view query in gp_matview_aux for view matching. #1117

my-ship-it merged 1 commit into from
May 23, 2025

Conversation

@avamingli
Copy link
Contributor

@avamingli avamingli commented May 21, 2025 •
edited by yjhjstz
Loading

Move materialized views SQLs for AQUMV from pg_rewrite to gp_matview_aux.
This would allow for quicker access and filtering, bypassing the evaluation of the system's 200 built-in rule entries that are often irrelevant.

before this commits:

set enable_answer_query_using_materialized_views = on; 
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)

Planning Time: 78.942 ms

after this commits:

explain(analyze, costs off, verbose) select count(a) from t_select;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Finalize Aggregate (actual time=4.000..4.000 rows=1 loops=1)
   Output: count(a)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual time=4.000..4.000 rows=3 loops=1)
         Output: (PARTIAL count(a))
         ->  Partial Aggregate (actual time=4.000..4.000 rows=1 loops=1)
               Output: PARTIAL count(a)
               ->  Seq Scan on public.t_select (actual time=4.000..4.000 rows=0 loops=1)
                     Output: a
 Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off'
 Planning Time: 0.623 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.670 ms
(15 rows)

Planning Time: 0.623 ms

Authored-by: Zhang Mingli [email protected]

Fixes #ISSUE_Number

What does this PR do?

Type of Change

  • Bug fix (non-breaking change)
  • New feature (non-breaking change)
  • Breaking change (fix or feature with breaking changes)
  • Documentation update

Breaking Changes

Test Plan

  • Unit tests added/updated
  • Integration tests added/updated
  • Passed make installcheck
  • Passed make -C src/test installcheck-cbdb-parallel

Impact

Performance:

User-facing changes:

Dependencies:

Checklist

Additional Context

CI Skip Instructions

yjhjstz
yjhjstz reviewed May 21, 2025
View reviewed changes
yjhjstz
yjhjstz reviewed May 23, 2025
View reviewed changes
@avamingli
[AQUMV] Store view query in gp_matview_aux for view matching.
e3fa8c0 
Move materialized views SQLs for AQUMV from pg_rewrite to
gp_matview_aux. This would allow for quicker access and
filtering, bypassing the evaluation of the system's 200
built-in rule entries that are often irrelevant.

before this commit:

set enable_answer_query_using_materialized_views = off;
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)

enable_answer_query_using_materialized_views = off
Planning Time: 0.462 ms

after this commit:
explain(analyze, costs off, verbose) select count(a) from
t_select;
                             QUERY PLAN
-----------------------------------------------------------------------
 Finalize Aggregate (actual time=4.000..4.000 rows=1 loops=1)
   Output: count(a)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual
time=4.000..4.000 rows=3 loops=1)
         Output: (PARTIAL count(a))
         ->  Partial Aggregate (actual time=4.000..4.000 rows=1 loops=1)
               Output: PARTIAL count(a)
               ->  Seq Scan on public.t_select (actual time=4.000..4.000
rows=0 loops=1)
                     Output: a
 Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
 Planning Time: 0.623 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.670 ms
(15 rows)

enable_answer_query_using_materialized_views = off
Planning Time: 0.623 ms

Authored-by: Zhang Mingli [email protected]
@my-ship-it my-ship-it merged commit bb6fef2 into apache:main May 23, 2025
26 checks passed
@avamingli avamingli deleted the view_store branch May 23, 2025 09:44
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@yjhjstz yjhjstz yjhjstz approved these changes

@my-ship-it my-ship-it my-ship-it approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@avamingli @yjhjstz @my-ship-it