GCP BigQuery query performance optimization when using ARRAY_AGG with large datasets in Go
I'm writing unit tests and I'm experiencing important performance optimization when executing a query in Google BigQuery that uses the `ARRAY_AGG` function on a dataset with over 10 million rows... The query hangs for several minutes before timing out, even though smaller datasets execute without any issues. Here's the query I'm using: ```sql SELECT user_id, ARRAY_AGG(order_id) AS order_ids FROM `my_project.my_dataset.orders` WHERE order_date >= '2023-01-01' GROUP BY user_id ORDER BY user_id ``` I'm running this query from a Go application using the `cloud.google.com/go/bigquery` library version `1.27.0`. I have increased the timeout settings in my client code, but that hasn't helped: ```go ctx, cancel := context.WithTimeout(context.Background(), 10*time.Minute) defaultClient, err := bigquery.NewClient(ctx, "my_project") if err != nil { log.Fatalf("Failed to create client: %v", err) } defer cancel() ``` Additionally, I have tried optimizing the query by adding indexes to the `user_id` and `order_date` fields, but the performance still suffers. I also looked into partitioning the table based on `order_date`, but I am unsure if this will significantly affect the performance of the `ARRAY_AGG` function. Is there a better way to structure this query, or should I consider using a different approach to aggregate large datasets to avoid timeouts? Any insights on optimizing performance in such scenarios would be greatly appreciated! This issue appeared after updating to Go 3.11. What's the best practice here? My team is using Go for this CLI tool. Thanks for taking the time to read this! I'm on Ubuntu 20.04 using the latest version of Go. Could someone point me to the right documentation?