#280 batched the heavy part of the relation write path: the forward write and the reverse pointers now go out in bulk (fast_write_forward_meta and apply_relation_pointers), which took relation_many_targets from ~18s to ~460ms. One bit stayed per-row though, the sidecar reindex. reindex_targets still loops and calls index_row_field once for every target document it touched, so 500 targets is 500 × (SELECT + DELETE + N INSERTs). That loop is all this issue is about.
Proposed solution: the pointer write already hands us the delta (which targets were added, which were removed), so build the sidecar delta from that and write it in two statements instead of a loop: one DELETE ... WHERE (row_id, field_id, value) IN (...) for the removals, one multi-row INSERT for the additions. The forward field can stay on index_row_field, it's a single row.
#280 batched the heavy part of the relation write path: the forward write and the reverse pointers now go out in bulk (
fast_write_forward_metaandapply_relation_pointers), which tookrelation_many_targetsfrom ~18s to ~460ms. One bit stayed per-row though, the sidecar reindex.reindex_targetsstill loops and callsindex_row_fieldonce for every target document it touched, so 500 targets is 500 × (SELECT + DELETE + N INSERTs). That loop is all this issue is about.Proposed solution: the pointer write already hands us the delta (which targets were added, which were removed), so build the sidecar delta from that and write it in two statements instead of a loop: one
DELETE ... WHERE (row_id, field_id, value) IN (...)for the removals, one multi-rowINSERTfor the additions. The forward field can stay onindex_row_field, it's a single row.