This is an odd implementation, deduping should be an O(N) operation and should use a hash table somewhere. Either a set or an object or a map and use the keys to deduplicate.

That being said, I thought we saw a clear fix? Why do you think changing the query would be so much slower? Generally speaking, it's always faster to do something at database level.

Also, as a consumer, getting the first 1000 slightly faster but getting the whole bucket slower doesn't help! I want the whole thing as fast as possible.

@eric-gt - any thoughts on this?

So my original clear fix turned out to be unsupported in BigQuery. It's impossible to SELECT DISTINCT on a single column, or GROUP BY on a single column in BigQuery's SQL implementation. SELECT DISTINCT does not work on repeatable columns (structs, arrays, etc) so we would have to group on every single column being returned, which would make the query much more verbose and hard to modify (e.g. removing or adding a column from the query would require edits in 4-5 different places in the query)
I can reimplement the deduplication function to use a hash table or take the plunge and rewrite the query to do the much more complex grouping operations.

@nandanrao let me know your thoughts on which course to take with this last change

@nandanrao the change to use an Object in the application code was only about an hour of work, so I went ahead and made the change. Unit and Postman checks of deduplication are passing.