OPTIMIZE as you alluded has two operations , Bin-packing and multi-dimensional clustering ( zorder)
- Bin-packing optimization is idempotent, meaning that if it is run twice on the same dataset, the second run has no effect
- Z-Ordering is not idempotent but aims to be an incremental operation. if no new data was added to a partition that was just Z-Ordered, another Z-Ordering of that partition will not have any effect.
With that in context, you'd want to check if your optimize job is CPU bound or memory bound. Anecdotal evidence shows that ESv3 series in Azure and r5a/r5d instances in AWS gives good cpu/mem ratio and is good for optimize - but really YMMV
