Following the lead of the possible bug report on PG17, we ran the queries with

SET max_parallel_workers_per_gather = 0

and that made the query return in 5 minutes without errors. Digging deeper we decided to review the work_mem, which was set by default to 4MB by RDS. We updated this value in the configuration to 64MB based loosely on this parameter guide, and it started working smoothly, returning in around 3 minutes.

SET work_mem TO '64MB';