Word Cloud unconditional secondary sort prevents Druid TopN optimization, causing query timeout

[bdonovan1]

Bug description

Description

When sort_by_metric is enabled on a Word Cloud chart, buildQuery.ts unconditionally appends a secondary ORDER BY [series, ASC] alongside the metric sort. On Apache Druid, any multi-column ORDER BY prevents the native TopN query optimization, forcing a full GroupBy scan over the entire dataset before applying LIMIT. On high-cardinality dimensions this can cause dramatic query slowdowns and timeouts.

Root Cause

In superset-frontend/plugins/plugin-chart-word-cloud/src/plugin/buildQuery.ts:

if (sort_by_metric && metric) {
  orderby.push([metric, false]);
}
if (series) {
  orderby.push([series, true]);  // ← always added, even when sort_by_metric is true
}

When sort_by_metric=true, this generates:

ORDER BY term_count DESC, search_term ASC

Druid's TopN algorithm requires ordering by a single aggregate metric. The secondary dimension sort forces the full GroupBy execution path regardless of dataset size.

Steps to Reproduce

1. Create a Word Cloud chart backed by a Druid datasource with a high-cardinality string dimension
2. Enable Sort by metric
3. Load the chart — on large datasets it will time out with "Unknown error (Unknown)"

Without "Sort by metric", the chart loads correctly (Druid uses TopN). With it enabled, the same query triggers a full GroupBy scan.

Proposed Fix

Make the secondary dimension sort mutually exclusive with sort_by_metric:

if (sort_by_metric && metric) {
  orderby.push([metric, false]);
} else if (series) {
  orderby.push([series, true]);
}

This preserves alphabetical ordering when sort_by_metric is disabled, while allowing Druid to use TopN optimization when metric sorting is requested. The secondary sort is also unnecessary for word cloud rendering, since word size is determined by metric value rather than series order.

Additional Notes

The existing test/buildQuery.test.ts does not cover sort_by_metric or orderby behavior. A PR for this fix should add test coverage for both cases.

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hi @bdonovan1! I'm Dosu and I’m helping the superset team.

Looking at the current code, the buildQuery.ts implementation on master doesn't actually match the bug description. The current logic is [1]:

export default function buildQuery(formData: WordCloudFormData) {
  const { metric, sort_by_metric } = formData;

  return buildQueryContext(formData, baseQueryObject => [
    {
      ...baseQueryObject,
      ...(sort_by_metric && { orderby: [[metric, false]] }),
    },
  ]);
}

There's no secondary series sort being appended — when sort_by_metric is true, only [[metric, false]] is set as the orderby. When it's false, no explicit orderby is added at all. So the dual ORDER BY term_count DESC, search_term ASC pattern described in the issue shouldn't be produced by this code path.

That said, it's worth noting that buildQueryContext or the baseQueryObject itself could be injecting a default orderby that gets overridden (or not) depending on the spread order. If baseQueryObject already contains an orderby with the series column, the spread ...baseQueryObject would set it first, and the conditional ...(sort_by_metric && { orderby: ... }) would override it — which should be correct. But if sort_by_metric is false, whatever default orderby exists in baseQueryObject would pass through.

Could the secondary sort be coming from the baseQueryObject defaults or from a downstream SQL generation layer rather than this buildQuery.ts file? It might help to inspect the actual SQL query generated (via the SQL Lab or query logs) to confirm where the multi-column ORDER BY originates. The test file also has no coverage for sort_by_metric or orderby behavior at all [2], so adding tests there is a good idea regardless.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}
   

[bdonovan1]

Hi @dosu !

The code that you linked does not appear to be the latest master, as far as I can tell. It is referencing commit 829e4d92d91ceae4b43b1ed3b063ffe45377799c, which is a commit from July of 2025. You can see the latest code here.

Actually, the order-by behavior was modified ~2 months ago in this commit:
fca8a49#diff-7ade1e3a604827404c12df312a453823f67b61d24223b4d799c9a7b220a9b32f

That is consistent with my own observations - our Word Cloud was working correctly until ~2 months ago, then we began to experience this performance problem. I will also tag @rusackas , who was the author of that change. I am planning to submit a PR to fix the change + add unit tests.

[dosubot]

Thanks for the correction — you're right that the code I referenced was stale. I can see now that the commit you linked (fca8a49) is from PR #37459 ("feat: auto refresh dashboard"), merged around 2 months ago. That PR introduced a pattern across multiple chart types (Sankey, Graph, Tree, Treemap) that unconditionally appends column-based ORDER BY clauses alongside the metric sort to ensure consistent data ordering during dashboard auto-refresh.

Looking at the Sankey chart diff from that commit as a representative example, the new pattern does exactly what you described:

if (sort_by_metric && metric) {
  orderby.push([metric, false]);
}
[source, target].forEach(column => {
  if (column) {
    orderby.push([column, true]);  // always added
  }
});

If the same pattern was applied to the Word Cloud buildQuery.ts, that would explain the dual ORDER BY breaking Druid's TopN optimization. Your proposed fix of making the secondary sort mutually exclusive with sort_by_metric makes sense for the Word Cloud case, since the secondary dimension sort is unnecessary for rendering and harmful for Druid performance.

Looking forward to your PR!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}