Skip to content

[AQUMV]Allow to use normal materialized views to answer query. #528

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
Jul 24, 2024
Merged

[AQUMV]Allow to use normal materialized views to answer query. #528

my-ship-it merged 1 commit into from
Jul 24, 2024

Conversation

@avamingli
Copy link
Contributor

@avamingli avamingli commented Jul 19, 2024

As we have enabled materialized view data status maintenance, normal materialized views could also be used to answer query if their data status is up to date.
There is no difference between IVM and normal materialized views to answer query.

example, compute agg from a normal materialized view:

create table t1(c1 int, c2 int, c3 int) distributed by (c1);

create materialized view normal_mv_t1 as
  select c3 as mc3, c1 as mc1 from t1 where c1 > 90;

set enable_answer_query_using_materialized_views = on; 
explain(costs off, verbose)
select count(c3) from t1 where c1 > 90;
                    QUERY PLAN
------------------------------------------------------
 Finalize Aggregate
   Output: count(mc3)
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Output: (PARTIAL count(mc3))
         ->  Partial Aggregate
               Output: PARTIAL count(mc3)
               ->  Seq Scan on aqumv.normal_mv_t1
                     Output: mc3, mc1
 Optimizer: Postgres query optimizer

BTW, fix some bugs when we didn't find a tuple from gp_matview_aux which could be possible as we enable using normal
materialized view(ex: created by bootstrap).

#0  __pthread_kill_implementation (no_tid=0, signo=11, threadid=140360340483584) at ./nptl/pthread_kill.c:44
#1  __pthread_kill_internal (signo=11, threadid=140360340483584) at ./nptl/pthread_kill.c:78
#2  __GI___pthread_kill (threadid=140360340483584, signo=signo@entry=11) at ./nptl/pthread_kill.c:89
#3  0x00007fa8337bf476 in __GI_raise (sig=11) at ../sysdeps/posix/raise.c:26
#4  0x00007fa834924214 in StandardHandlerForSigillSigsegvSigbus_OnMainThread (
    processName=0x7fa83504665b "Master process", postgres_signal_arg=11) at elog.c:5377
#5  0x00007fa83471d162 in CdbProgramErrorHandler (postgres_signal_arg=11) at postgres.c:3818
#6  <signal handler called>
#7  0x00007fa8341ef70a in MatviewIsGeneralyUpToDate (mvoid=12317) at gp_matview_aux.c:441
#8  0x00007fa8345a7770 in answer_query_using_materialized_views (root=0x55dc22867e78, current_rel=0x55dc228687d8,
    qp_callback=0x7fa83458e7fb <standard_qp_callback>, qp_extra=0x7ffdde5c77c0) at aqumv.c:183
#9  0x00007fa83458a680 in grouping_planner (root=0x55dc22867e78, tuple_fraction=0) at planner.c:1890
#10 0x00007fa8345898a5 in subquery_planner (glob=0x55dc22814dc8, parse=0x55dc2284bf38, parent_root=0x0,
    hasRecursion=false, tuple_fraction=0, config=0x55dc22867e00) at planner.c:1344
#11 0x00007fa834587a07 in standard_planner (parse=0x55dc2284bf38,
    query_string=0x55dc2270f9b0 "explain(verbose, costs off) select * from aqumv_t1 where c1 = 2;",
    cursorOptions=2048, boundParams=0x0) at planner.c:562
#12 0x00007fa8345872e8 in planner (parse=0x55dc22710cd8,
    query_string=0x55dc2270f9b0 "explain(verbose, costs off) select * from aqumv_t1 where c1 = 2;",
    cursorOptions=2048, boundParams=0x0) at planner.c:332
#13 0x00007fa834717bce in pg_plan_query (querytree=0x55dc22710cd8,

Authored-by: Zhang Mingli [email protected]

fix #ISSUE_Number

Change logs

Describe your change clearly, including what problem is being solved or what feature is being added.

If it has some breaking backward or forward compatibility, please clary.

Why are the changes needed?

Describe why the changes are necessary.

Does this PR introduce any user-facing change?

If yes, please clarify the previous behavior and the change this PR proposes.

How was this patch tested?

Please detail how the changes were tested, including manual tests and any relevant unit or integration tests.

Contributor's Checklist

Here are some reminders and checklists before/when submitting your pull request, please check them:

  • Make sure your Pull Request has a clear title and commit message. You can take git-commit template as a reference.
  • Sign the Contributor License Agreement as prompted for your first-time contribution(One-time setup).
  • Learn the coding contribution guide, including our code conventions, workflow and more.
  • List your communication in the GitHub Issues or Discussions (if has or needed).
  • Document changes.
  • Add tests for the change
  • Pass make installcheck
  • Pass make -C src/test installcheck-cbdb-parallel
  • Feel free to request cloudberrydb/dev team for review and approval when your PR is ready🥳

@avamingli avamingli requested review from my-ship-it and yjhjstz July 19, 2024 10:06
@avamingli
[AQUMV] Allow to use normal materialized views to answer query.
95d6d13 
As we have enabled materialized view data status maintenance,
normal materialized views could also be used to answer query
if their data status is up to date.
There is no difference between IVM and normal materialized
views to answer query.

example, compute agg from a normal materialized view:

create table t1(c1 int, c2 int, c3 int) distributed by (c1);

create materialized view normal_mv_t1 as
  select c3 as mc3, c1 as mc1 from t1 where c1 > 90;

set enable_answer_query_using_materialized_views = on;
explain(costs off, verbose)
select count(c3) from t1 where c1 > 90;
                    QUERY PLAN
------------------------------------------------------
 Finalize Aggregate
   Output: count(mc3)
   ->  Gather Motion 3:1  (slice1; segments: 3)
         Output: (PARTIAL count(mc3))
         ->  Partial Aggregate
               Output: PARTIAL count(mc3)
               ->  Seq Scan on aqumv.normal_mv_t1
                     Output: mc3, mc1
 Optimizer: Postgres query optimizer

Authored-by: Zhang Mingli [email protected]
@avamingli avamingli changed the title Allow to use normal materialized views to answer query. [AQUMV]Allow to use normal materialized views to answer query. Jul 19, 2024
my-ship-it
my-ship-it reviewed Jul 23, 2024
View reviewed changes
@my-ship-it my-ship-it merged commit 52ead45 into apache:main Jul 24, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

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

@yjhjstz yjhjstz Awaiting requested review from yjhjstz

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@avamingli @my-ship-it