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 work_mem
:
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?
col1
andcol2
would be useful? What aboutcol3
?where col3 <= 20
reduces the number of rows substantially (to maybe 10 or 20% of the total number of rows), then this would be the only index that could help.