[Bug]

Offen
  • Stimmt etwas nicht?
#2,255 1 Kommentar 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 2 Tagen

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
40/100
Issue-Typ
Bug
Klarheit
Größtenteils klar
Aktivitätsstatus
Aktiv
Tech-Stack
c, postgresql
Bereich
backend, databases

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

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
Vorherrschende Sprache
C
Sterne
1.4k
Forks
268
Ø Merge
4 T. 3 Std.
Gemergte PRs (30 T.)
78

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus apache/cloudberry

Alle Issues in apache/cloudberry

Ähnliche Issues

Weitere Issues zu C

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.