Model.rebuild! slow in postgres for larger collections with many roots

[morus]

We have an issue with Model.rebuild! beeing slow on a dataset with some 10000 records and 4000 root elements (no parent) in a postgres database.

We tracked that down to statements updating the sort order column for all root elements over and over again
(looks like once for every root element, so its ~4k updates of 4k records) and postgres is getting slower and
slower on that, probably because of table/index bloat or other issues.

The update is done through the reorder_with_parent_id in the PostgresSQLAdapter module in numeric_order_support.rb .

Suggested fix:

Most of these updates could be avoided if the (2nd) where clause would be amended by a check if the value in the order column changes with the update (postgres executes a real update with a copied record even if nothing changes in the record) as most of the writes are skipped then.

Would you be open to such a change? I could open a PR then.
Of course we are open to other solutions, but several hours for the rebuild! is not a nice behavior.
This went down to some three minutes with the change.

[mceachen]

Please see https://rubygems.org/gems/pg_closure_tree_rebuild for another solution. I think the thinking with that gem was the solution was too heavyweight to include in this gem, but if you find a workaround that is reasonable, and tests pass, a PR would be splendid.

[morus]

I opened a PR (#278).

My change is much more a local optimization and does not change the logical structure.

I tried to look into the pg closure tree rebuild gem but failed to integrate it into our migration because of the different db connection that one uses. So I didn't do a speed comparison.

[mceachen]

Thanks for the PR!