SELECT
	CASE
		WHEN "t2"."def_m_c843fbc362bd9fe20d49b7dd43d842a5" THEN "t2"."m_c843fbc362bd9fe20d49b7dd43d842a5"
		ELSE NULL
	END AS "m_1",
	CASE
		WHEN "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b" THEN "t6"."m_c3da1b901bc068c18076ab1d3c28da9b"
		ELSE NULL
	END AS "m_2",
	CASE
		WHEN "t2"."def_m_c843fbc362bd9fe20d49b7dd43d842a5"
		OR "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b" THEN CAST(
			CASE
				WHEN "t2"."def_m_c843fbc362bd9fe20d49b7dd43d842a5"
				OR "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b" THEN CASE
					WHEN "t2"."def_m_c843fbc362bd9fe20d49b7dd43d842a5" THEN COALESCE("t2"."m_c843fbc362bd9fe20d49b7dd43d842a5", 0.0)
					ELSE 0.0
				END - CASE
					WHEN "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b" THEN COALESCE("t6"."m_c3da1b901bc068c18076ab1d3c28da9b", 0.0)
					ELSE 0.0
				END
				ELSE 0.0
			END AS DOUBLE PRECISION
		) / NULLIF(
			CASE
				WHEN "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b" THEN COALESCE("t6"."m_c3da1b901bc068c18076ab1d3c28da9b", 0.0)
				ELSE 0.0
			END,
			0
		)
		ELSE NULL
	END AS "m_3"
FROM
	(
		SELECT
			SUM("net_revenue") AS "m_c843fbc362bd9fe20d49b7dd43d842a5",
			TRUE AS "def_m_c843fbc362bd9fe20d49b7dd43d842a5"
		FROM
			"bookee_demo"."gd_center_sales"
		WHERE
			"created_at" >= (TO_TIMESTAMP('2026-2', 'YYYY-MM')::TIMESTAMP) AT TIME ZONE 'Etc/UTC'
			AND "created_at" < (DATEADD ('month', 1, TO_TIMESTAMP('2026-2', 'YYYY-MM')::TIMESTAMP)) AT TIME ZONE 'Etc/UTC'
			AND "created_at" < (DATEADD ('day', 1, TO_TIMESTAMP('2026-2-20', 'YYYY-MM-DD')::TIMESTAMP)) AT TIME ZONE 'Etc/UTC'
		GROUP BY
			CAST(NULL AS INTEGER)
	) AS "t2",
	(
		SELECT
			SUM("net_revenue") AS "m_c3da1b901bc068c18076ab1d3c28da9b",
			TRUE AS "def_m_c3da1b901bc068c18076ab1d3c28da9b"
		FROM
			"bookee_demo"."gd_center_sales"
		WHERE
			"created_at" >= (DATEADD ('month', -1, TO_TIMESTAMP('2026-2', 'YYYY-MM')::TIMESTAMP)) AT TIME ZONE 'Etc/UTC'
			AND "created_at" < (DATEADD ('month', 1, DATEADD ('month', -1, TO_TIMESTAMP('2026-2', 'YYYY-MM')::TIMESTAMP))) AT TIME ZONE 'Etc/UTC'
			AND "created_at" < (DATEADD ('day', 1, DATEADD ('month', -1, TO_TIMESTAMP('2026-2-20', 'YYYY-MM-DD')::TIMESTAMP))) AT TIME ZONE 'Etc/UTC'
			AND CONVERT(TIMESTAMP, DATE_TRUNC('MONTH', ("created_at") AT TIME ZONE 'Etc/UTC')) IS NOT NULL
			AND CONVERT(TIMESTAMP, DATE_TRUNC('DAY', ("created_at") AT TIME ZONE 'Etc/UTC')) IS NOT NULL
		GROUP BY
			CAST(NULL AS INTEGER)
	) AS "t6"
WHERE
	"t2"."def_m_c843fbc362bd9fe20d49b7dd43d842a5"
	OR "t6"."def_m_c3da1b901bc068c18076ab1d3c28da9b";

this query generate by gooddata for a visualisation is very slow for us, its a simple net revenue calcultion and data source is redshift. Does anyone know how to optimise this? what are some ways of improving the query run time?

You’re hitting a known pattern: GoodData’s generated SQL is correct but not always optimal for Redshift, especially with time-zone wrapping and large fact tables. The knowledge sources don’t show a way to “rewrite” this specific query from inside GoodData, but they do describe several levers you can use to improve performance. Below are the options that are documented: ------------------------- 1. Optimize the Redshift table for this kind of query GoodData is scanning

bookee_demo.gd_center_sales

with multiple

created_at

filters. For Redshift, performance depends heavily on table design: • Denormalize into a wide fact table (which you already seem to have) so queries avoid large joins. [Improve performance] • Choose an appropriate sort key: ◦ Use

created_at

as a sort key if most queries filter by date (as in your example). [Redshift source] • Choose a good distribution style: ◦ Use a high-cardinality column as the distribution key so data is evenly distributed. [Redshift source] • Ensure data types are accurate (e.g., `TIMESTAMP`/`TIMESTAMPTZ` instead of generic

VARCHAR(2000)

) to help compression and pruning. [Improve performance] ------------------------- 2. Use database-side performance features For heavy aggregations like

SUM(net_revenue)

over large ranges: • Materialized views / pre-aggregated tables Create a materialized view or aggregate table that pre-aggregates

net_revenue

by the grain you usually query (e.g., day or month). [Improve performance] • Then register that aggregate table in the LDM and enable Aggregate Awareness so GoodData routes queries to the smaller table instead of the detailed fact. [Aggregate awareness] This is often the biggest win for “simple but large” revenue metrics. ------------------------- 3. Indexing / keys on filter columns While Redshift doesn’t use traditional B-tree indexes like OLTP DBs, the general guidance still applies: • Make sure the columns used in filters and joins (here,

created_at

and any join keys) are part of sort/distribution design. • GoodData’s guidance is to index (or otherwise optimize) only the most impactful columns used in joins and common filters, not every column. [DB performance] ------------------------- 4. Time filters & time zone wrapping Your query shows multiple

TO_TIMESTAMP(...) AT TIME ZONE 'Etc/UTC'

expressions. For Redshift, function-wrapped literals can prevent efficient use of sort-key zone maps. The knowledge sources confirm: • There is no documented setting to disable the

timezone()

/

to_timestamp()

wrapping or to force “raw” timestamp literals for Redshift. [Redshift source] • Time zone behavior is controlled only by: ◦ Time zone hierarchy (org → workspace → user), ◦ Column data type (

TIMESTAMPTZ

vs `TIMESTAMP`/`DATE`), not by a per–data-source SQL-generation toggle. [Manage time zones] So you cannot directly change this SQL shape from the UI. If you’ve already confirmed that literal timestamps are ~50× faster on your cluster, this is something to raise with GoodData Support as a product/SQL-generator issue, including: • The exact

/debug

SQL (what you pasted), • Your hand-written equivalent with literals, • Both

EXPLAIN

plans and cost difference, • Confirmation that

created_at

is the sort key and data is stored in UTC. [Query debug; Redshift source] ------------------------- 5. Check for systemic issues and metric design Even for a “simple” net revenue metric, other factors can slow things down: • Metric order in the visualization The first metric drives the main subquery. If this metric is heavy or forces complex filters, it can slow everything. Try moving a simpler metric first and see if the generated SQL becomes lighter. [Metric order] • Join paths from filters If your metric or visualization uses filters that require long join paths (fact → multiple dimensions), that can add complexity even if the core aggregation is simple. Simplifying or moving such logic closer to the fact table (e.g., precomputed flags) can help. [Metric join path] • LDM mapping warnings Check the LDM editor for mapping/validation warnings; unresolved mappings can cause repeated resolution attempts and slow queries. [LDM mapping impact] ------------------------- 6. Validate where the slowness is To be sure the bottleneck is in Redshift (and not in GoodData’s post-processing): 1. Open the visualization in Analytical Designer and replace

/edit

with

/debug

to download the debug file. [Query debug] 2. Run the exact SQL on Redshift and compare runtime. 3. Use Redshift performance tools (query history,

EXPLAIN

) to see if: ◦ It’s doing a full scan vs using sort keys, ◦ There’s queueing or resource contention. [Improve performance; Redshift source] ------------------------- Summary of actionable steps: • Ensure

created_at

is a sort key and table is well distributed. • Consider a materialized view / aggregate table for

net_revenue

by date and wire it into GoodData via Aggregate Awareness. • Review metric order and any complex filters in the visualization. • Use

/debug

+ Redshift

EXPLAIN

to confirm the exact bottleneck. • If the main issue is the

AT TIME ZONE

wrapping, escalate to GoodData Support with the two query versions and plans; there is no documented self-service switch to change that behavior today. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.