GitHub user FrancescoCastaldi added a comment to the discussion: Optimize
Dashboard Filter Performance via Precomputed/Extract-based Caching
The challenge you describe is fundamental to SQL-based analytical engines
(Trino, Presto, Hive) where query compilation and data scanning incur
significant latency on every modified WHERE clause.
While Superset core does not currently feature an internal in-memory columnar
query engine (like Tableau Data Engine / Hyper or DuckDB WASM in the browser),
you can achieve extract-like performance and avoid query storms through several
existing architectural capabilities:
### 1. Celery Cache Warmup with Pre-warmed Combinations
Superset provides a built-in background cache warming task located in
`superset/tasks/cache.py`. You can schedule Celery beat workers to execute
`warm_up_cache` on specific dashboards and filter tags during off-peak hours:
```python
# In superset_config.py
from celery.schedules import crontab
CELERYBEAT_SCHEDULE = {
"cache-warmup-hourly": {
"task": "cache-warmup",
"schedule": crontab(minute=0), # hourly
"kwargs": {
"strategy_name": "DashboardTagsStrategy",
"tags": ["warmup", "executive_kpis"],
},
},
}
```
This primes the Redis cache (`DATA_CACHE_CONFIG`) before users open the
dashboard.
### 2. Intermediate Semantic Extract Acceleration (Trino / Iceberg / ClickHouse)
Rather than caching raw data in Redis key-value blobs and trying to filter JSON
client-side, the scalable architecture for Presto/Trino consists of creating
materialized extract tables or Iceberg views:
1. Schedule a nightly CTAS or Airflow job creating aggregated extracts.
2. Point your primary dashboard charts to this pre-aggregated dataset.
3. Configure `CACHE_DEFAULT_TIMEOUT = 86400` on the dataset settings in
Superset, allowing filter combinations within that pre-aggregated dataset to
return in milliseconds.
### 3. Client-Side Cross-Filtering and In-Memory Filtering
For high-interactivity dashboards with moderately sized datasets (under 50,000
rows):
- Enable the feature flag `DASHBOARD_CROSS_FILTERS: True`.
- Use the Table chart or ECharts bar/pie charts as cross-filter emitters.
- Once the initial query resolves, cross-filtering applies in-memory in the
browser without re-querying Trino or Redis for every selection change.
### 4. Jinja Macro Optimization with Cache Key Normalization
If specific filter values do not alter the broader dataset aggregation, you can
use Jinja templating with `cache_key_wrapper` to avoid cache key explosion:
```sql
SELECT
dimension,
SUM(metric)
FROM my_extract_table
WHERE 1=1
{% if filter_values('region') %}
AND region IN {{ filter_values('region') | where_in }}
{% endif %}
GROUP BY dimension
```
Combining these strategies provides sub-second interactive response times while
shielding your data warehouse from redundant query loads.
GitHub link:
https://github.com/apache/superset/discussions/41852#discussioncomment-19164768
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
