I'm profiling a query across two systems (same number of nodes, but different hardware).
On my old system, this query runs in 30 minutes. On the new system, it takes 1+ hours. The new system was COPY CLUSTER'd from the old, and the explain plans are identical, so I decided to run a profile
I took a look at various counters, as well as avg/max execution times between the two, and it seems like the Root operator is the most dramatic. The average execution time for the Root operator almost makes up the entire difference in total execution time. What exactly is happening in the root operator?