bug: Random excessive query duration - guidance on how to build replica for debug

[rad-pat]

Search before asking

• I had searched in the issues and found no similar issues.

Version

v1.2.699-nightly

What's Wrong?

Seemingly randomly, queries will take a very long time, or just fail to complete. Unfortunately, for each of the instances we see, if I try to extract the data to build a replica, the replica does not exhibit the same issue so it is difficult to sensibly report the problem. Sometimes, but not always, rebuilding the tables for these queries will fix the problem.

Could you please provide some advice on how to report these sort of problems if a replica is not possible? If this involves turning on some debug info then we're happy to do that as the queries that do not return are very frustrating.

E.g. Current issue is a simple group by on a 700k record table, it does not return. Remove a timestamp field (4th col, see below) from the grouping and it runs straight away. However, take the data into a replica, the original aggregation works just fine. 🤷‍♂️

SELECT CAST(t_group.run_id AS VARCHAR) AS run_id
	,CAST(t_group.run_day AS VARCHAR) AS run_day
	,CAST(t_group.run_sequence AS VARCHAR) AS run_sequence
	,CAST(t_group."timestamp" AS TIMESTAMP) AS "timestamp"
	,CAST(t_group."PLAID_ID" AS VARCHAR) AS "PLAID_ID"
	,CAST(t_group."Period" AS VARCHAR) AS "Period"
	,CAST(t_group."Date" AS VARCHAR) AS "Date"
	,CAST(t_group."Run ID" AS VARCHAR) AS "Run ID"
	,CAST(t_group."CHARGE_GROUP" AS VARCHAR) AS "CHARGE_GROUP"
	,CAST(t_group."Charge Type" AS VARCHAR) AS "Charge Type"
	,CAST(t_group."Charge Type Alias" AS VARCHAR) AS "Charge Type Alias"
	,CAST(t_group."Sender Entity" AS VARCHAR) AS "Sender Entity"
	,CAST(t_group."Sender Country" AS VARCHAR) AS "Sender Country"
	,CAST(t_group."Sender Region" AS VARCHAR) AS "Sender Region"
	,CAST(t_group."Biller Entity" AS VARCHAR) AS "Biller Entity"
	,CAST(t_group."Biller Country" AS VARCHAR) AS "Biller Country"
	,CAST(t_group."Biller Region" AS VARCHAR) AS "Biller Region"
	,CAST(t_group."Receiver Entity" AS VARCHAR) AS "Receiver Entity"
	,CAST(t_group."Receiver Country" AS VARCHAR) AS "Receiver Country"
	,CAST(t_group."Receiver Region" AS VARCHAR) AS "Receiver Region"
	,CAST(t_group."Reference Number" AS VARCHAR) AS "Reference Number"
	,CAST(t_group."ISO_Code_Sender" AS VARCHAR) AS "ISO_Code_Sender"
	,CAST(t_group."Country_Sender" AS VARCHAR) AS "Country_Sender"
	,CAST(t_group."Lat_Sender" AS VARCHAR) AS "Lat_Sender"
	,CAST(t_group."Long_Sender" AS VARCHAR) AS "Long_Sender"
	,CAST(t_group."ISO_Code_biller" AS VARCHAR) AS "ISO_Code_biller"
	,CAST(t_group."Country_biller" AS VARCHAR) AS "Country_biller"
	,CAST(t_group."Lat_biller" AS VARCHAR) AS "Lat_biller"
	,CAST(t_group."Long_biller" AS VARCHAR) AS "Long_biller"
	,CAST(t_group."ISO_Code_receiver" AS VARCHAR) AS "ISO_Code_receiver"
	,CAST(t_group."Country_receiver" AS VARCHAR) AS "Country_receiver"
	,CAST(t_group."Lat_receiver" AS VARCHAR) AS "Lat_receiver"
	,CAST(t_group."Long_receiver" AS VARCHAR) AS "Long_receiver"
	,CAST(sum(t_group."Value_USD") AS DECIMAL(38, 10)) AS "Value_USD_HEADER"
FROM t_group
WHERE t_group."Side" = 'AR'
	AND t_group."Line Type" = 'HEADER'
GROUP BY t_group.run_id
	,t_group.run_day
	,t_group.run_sequence
	,t_group."timestamp"
	,t_group."PLAID_ID"
	,t_group."Period"
	,t_group."Date"
	,t_group."Run ID"
	,t_group."CHARGE_GROUP"
	,t_group."Charge Type"
	,t_group."Charge Type Alias"
	,t_group."Sender Entity"
	,t_group."Sender Country"
	,t_group."Sender Region"
	,t_group."Biller Entity"
	,t_group."Biller Country"
	,t_group."Biller Region"
	,t_group."Receiver Entity"
	,t_group."Receiver Country"
	,t_group."Receiver Region"
	,t_group."Reference Number"
	,t_group."ISO_Code_Sender"
	,t_group."Country_Sender"
	,t_group."Lat_Sender"
	,t_group."Long_Sender"
	,t_group."ISO_Code_biller"
	,t_group."Country_biller"
	,t_group."Lat_biller"
	,t_group."Long_biller"
	,t_group."ISO_Code_receiver"
	,t_group."Country_receiver"
	,t_group."Lat_receiver"
	,t_group."Long_receiver"

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[sundy-li]

However, take the data into a replica, the original aggregation works just fine. 🤷‍♂️

What does it mean take the data into a replica, I don't understand.

[rad-pat]

However, take the data into a replica, the original aggregation works just fine. 🤷‍♂️

What does it mean take the data into a replica, I don't understand.

I mean, I take the table data from the Production system into a development one in attempt to replicate the bug.

[sundy-li]

Current issue is a simple group by on a 700k record table, it does not return

Hi, You can attach the log files (with info level).

[rad-pat]

OK, we'll see if the log files will do the trick, thanks. At what point after the query started should we stop the log file extract? I believe it spits an ERROR out after 300s if the query has not completed. Does that make for a good cut-off point?

[rad-pat]

Closed in favour of #17036 to report issue with logs.