[Bug]

Ouverte
  • Quelque chose ne va pas ?
#2,255 1 commentaire 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 2 jours

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
40/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
Active
Stack technique
c, postgresql
Domaine
backend, databases

Piste de recherche

Start with src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp and the optimizer_enable_multiple_distinct_aggs definition in guc_gp.c; the issue identifies the guard that causes fallback. Check how the optimizer handles multiple distinct qualified aggregates and review existing tests for that behavior. Done means establishing whether enabling the option is supported and safe, with evidence about plan behavior and the reason it defaults off.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

type: Bug
Apache Cloudberry version
Environment
  • Cloudberry version: <Ausgabe von SELECT version(); einfügen>
  • Segments: 4
  • optimizer = on
  • join_collapse_limit = 20, from_collapse_limit = 20 (defaults)
What happened
Environment
  • Cloudberry version: <Ausgabe von SELECT version(); einfügen>
  • Segments: 4
  • optimizer = on
  • join_collapse_limit = 20, from_collapse_limit = 20 (defaults)
Description

A BI tool (Yellowfin) generates queries with several COUNT(DISTINCT ...) on different columns.
GPORCA refuses these queries and falls back to the Postgres-based planner:

INFO:  GPORCA failed to produce a plan, falling back to planner
DETAIL:  Feature not supported: Multiple Distinct Qualified Aggregates are disabled in the optimizer

In our case the query joins ~20 tables (LEFT JOINs) with 7 COUNT(DISTINCT ...) and a wide GROUP BY.
After the fallback, the Postgres planner (with join_collapse_limit = 20) needs a very long time to plan:

Planning Time: 192018.556 ms
Optimizer: Postgres query optimizer
Execution Time: 50.880 ms

So the fallback itself turns a trivial query into a ~3 minute planning-only operation.

Questions
  1. Is this limitation still in place in the latest Cloudberry release, or is there a version where ORCA handles multiple DQAs by default?
  2. Is it safe/supported to set optimizer_enable_multiple_distinct_aggs = on in production (e.g. per role), given it is classified as a developer option? Are there known issues or plan-quality regressions (CXformGbAggWithMDQA2Join)?
  3. Is there a reason why the default is off (performance, correctness)?
  4. Are there recommendations to limit the planning time after an ORCA fallback for queries with many joins (e.g. lowering join_collapse_limit / from_collapse_limit for such roles)?

Thanks!

What you think should happen instead

No response

How to reproduce
Minimal reproduction
CREATE TABLE t_mdqa (a int, b int, c int) DISTRIBUTED BY (a);
SET optimizer = on;
SET optimizer_trace_fallback = on;
EXPLAIN SELECT count(DISTINCT a), count(DISTINCT b) FROM t_mdqa GROUP BY c;
-- -> falls back to Postgres-based planner

As far as I can see, the check is still present in current main
(src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp:
if (1 < num_dqa && !optimizer_enable_multiple_distinct_aggs)),
and optimizer_enable_multiple_distinct_aggs is defined as a DEVELOPER_OPTIONS GUC in guc_gp.c.

Operating System

NAME="Red Hat Enterprise Linux" VERSION="8.10 (Ootpa)" ID="rhel" ID_LIKE="fedora" VERSION_ID="8.10" PLATFORM_ID="platform:el8" PRETTY_NAME="Red Hat Enterprise Linux 8.10 (Ootpa)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos" HOME_URL="https://www.redhat.com/" DOCUMENTATION_URL="https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8" BUG_REPORT_URL="https://issues.redhat.com/" REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8" REDHAT_BUGZILLA_PRODUCT_VERSION=8.10 REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux" REDHAT_SUPPORT_PRODUCT_VERSION="8.10" Red Hat Enterprise Linux release 8.10 (Ootpa) Red Hat Enterprise Linux release 8.10 (Ootpa)

Anything else

No response

Are you willing to submit PR?
  • Yes, I am willing to submit a PR!
Code of Conduct
Langage dominant
C
Étoiles
1.4k
Forks
268
Merge moyen
4 j 3 h
PR mergées (30 j)
78

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de apache/cloudberry

Toutes les issues de apache/cloudberry

Issues similaires

Plus d'issues C

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.