m0000800_alter_product_version_range_scheme is way too slow

[ctron]

Having the standard data imported, m0000800_alter_product_version_range_scheme fails to complete withing 2 days.

The problem is the update statement, which has to process the following two tables:

trustify=# select count(*) from version_range;
   count   
-----------
 243556450
trustify=# select count(*) from product_version_range;
   count   
-----------
 145018502

The statement is:

UPDATE version_range SET version_scheme_id = 'rpm' WHERE id IN (SELECT version_range_id FROM product_version_range)

[JimFuller-RedHat]

the problem is that that sql query is getting executed for each row
something like a join

UPDATE version_range
SET version_scheme_id = 'rpm'
FROM version_range
JOIN product_version_range ON version_range.id = product_version_range.version_range_id;

is likely to be a lot better performing.

[dejanb]

That query is not valid in postgres

ERROR: table name "version_range" specified more than once

I played a bit with its variations, like

CREATE INDEX idx_product_version_range_id ON product_version_range(version_range_id);

with ranges_to_update as (
    select version_range.* from 
    version_range
    join product_version_range on version_range.id = product_version_range.version_range_id
)
update version_range
set version_scheme_id = 'rpm'
from ranges_to_update 
where version_range.id = ranges_to_update.id

But from the analysis it doesn't look it helps much. The bottleneck is updating 18M rows in the end.
Some of the solutions I found proposed are doing batch updates, but I don't see yet how that would fit our migrations.

[JimFuller-RedHat]

ah right, sorry I prob was writing psuedo psql ... let me write and test something ...

[dejanb]

Just FYI, the query from the previous comment, executed for me locally in 38min