avamingli opened a new pull request, #705:
URL: https://github.com/apache/cloudberry/pull/705
This commits enable answer query which has aggregation directly. Use the
results of view has aggregations to avoid compute those from origin table.
This may lead to significant efficiency gains if the SQL has a large amount
of data.
AQUMV will always return results immediately.
If we have a valid view like:
```sql
create materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
```
SQL:
```sql
select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t
where c1 > 90;
```
Could be rewritten to:
```sql
select mc4, mc1, mc2, mc3, abs((mc4 - 21)) from mv;
```
Plan:
```sql
explain(verbose, costs off)
select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t
where c1 > 90;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: mc4, mc1, mc2, mc3, (abs((mc4 - 21)))
-> Seq Scan on mv
Output: mc4, mc1, mc2, mc3, abs((mc4 - 21))
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Optimizer: Postgres query optimizer
(6 rows)
```
View query with Group By is not supported yet.
### HAVING clause process:
If some HAVING quals only exist in origin query and they could be computed
from view query's target list, then we could keep them like post_quals.But as
the view has aggregations, the additional quals should be moved to WHERE
instead of HAVING.
```sql
create table t(c1 int, c2 int, c3 int, c4 int);
create materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
```
SQL:
```sql
select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21)
> 0 and 2 > 1 and avg(c3) > 97;
```
Could be rewritten to (The HAVING clause has been rewritten to WHERE clause):
```sql
select mc4, mc1 from mv where mc3 > 97 and abs(mc4 - 21) > 0;
```
Plan:
```sql
explain(verbose, costs off)
select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21)
> 0 and 2 > 1 and avg(c3) > 97;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: mc4, mc1
-> Seq Scan on aqumv.mv
Output: mc4, mc1
Filter: ((mv.mc3 > '97'::numeric) AND (abs((mv.mc4 - 21)) > 0))
Optimizer: Postgres query optimizer
(7 rows)
```
There are two additional HAVING quals:
Expression: 2 > 1 (would be eliminated during planner). Expression:
abs(count(*) - 21) > 0, it could be computed from view as:
```sql
abs(mc4 - 21) > 0
```
And the new one is put to WHERE clause and acts as a Filter finally.
### ORDER BY clause:
There is a trick for ORDER BY for both origin query and view query. As we
has no Groupy By curretly, the aggregation results would be either one or zero
rows that make the Order By clause pointless.
We could avoid considering the sort columns if it's a junk for view matching.
### LIMIT clause:
As we have no group by for view with aggs now, the final result would be
either one or zero row.
LIMIT, OFFSET clause of origin query could be applied to view if there are
consts.
```sql
create incremental materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
```
Query:
```sql
select count(*), sum(c1) from t where c1 > 90 limit 2;
```
Could be rewritten to:
```sql
select mc4, mc1 from mv limit 2;
```
Authored-by: Zhang Mingli [email protected]
<!--Thank you for contributing!-->
<!--In case of an existing issue or discussions, please reference it-->
fix #ISSUE_Number
<!--Remove this section if no corresponding issue.-->
---
### 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](https://github.com/cloudberrydb/cloudberrydb/blob/main/.gitmessage)
template as a reference.
- [ ] Sign the Contributor License Agreement as prompted for your first-time
contribution(*One-time setup*).
- [ ] Learn the [coding contribution
guide](https://cloudberrydb.org/contribute/code), including our code
conventions, workflow and more.
- [ ] List your communication in the [GitHub
Issues](https://github.com/cloudberrydb/cloudberrydb/issues) or
[Discussions](https://github.com/orgs/cloudberrydb/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🥳
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
