Let's assume the table, intermediate10, is ~2.2 TB in size.
The following query takes ~4 days to run on a pretty powerful DB box (32 CPUs, 256 GB RAM) that is optimized to allow up to 32 parallel workers and has sufficiently high
create table subset as( select * from ( select *, RANK() OVER (PARTITION BY col1, col2 ORDER BY random()) AS rankct from intermediate10 where col3 <= 20 ) a where rankct <= 50 )
I understand that there is an extraneous subquery above, but that is an artifact from some logic I had to remove before posting. Regardless, this does not materially change the query plan or its efficiency.
I have an index on intermediate10:
CREATE INDEX ON intermediate10 (col1, col2);
but the query plan isn't using it:
Subquery Scan on a (cost=842128231.97..882842032.15 rows=361900446 width=1350) Filter: (a.rankct <= 50) -> WindowAgg (cost=842128231.97..869270765.42 rows=1085701338 width=1358) -> Sort (cost=842128231.97..844842485.32 rows=1085701338 width=1350) Sort Key: intermediate10.col1, intermediate10.col2, (random()) -> Seq Scan on intermediate10 (cost=0.00..314458488.95 rows=1085701338 width=1350) Filter: (col3 <= 20)
Interestingly, if the
order by random() is removed, the query will at least parallelize:
WindowAgg (cost=471738126.21..673065708.94 rows=1467031808 width=1350) -> Gather Merge (cost=471738126.21..647392652.30 rows=1467031808 width=1342) Workers Planned: 4 -> Sort (cost=471737126.15..472654021.03 rows=366757952 width=1342) Sort Key: col1, col2 -> Parallel Seq Scan on intermediate10 (cost=0.00..297073917.52 rows=366757952 width=1342)
but having that random selection of the 50 in the "sample" is not negotiable.
Needless to say, a 4-day runtime for this is unacceptable.
How could this be optimized?