Application start takes too much time

[4miners]

Expected behavior

The application should start immediately. We need to check if we need to reset mem_accounts2u_delegates table, because maybe after atomic blocks writes its not needed anymore. But if we need then the best approach is using those chain of queries:

DROP TABLE mem_accounts2u_delegates;
CREATE TABLE mem_accounts2u_delegates AS TABLE mem_accounts2delegates;
ALTER TABLE mem_accounts2u_delegates ADD CONSTRAINT "mem_accounts2u_delegates_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES mem_accounts(address) ON DELETE CASCADE;
CREATE INDEX "mem_accounts2u_delegates_accountId" ON mem_accounts2u_delegates("accountId");

Those should get executed in about a second.

Actual behavior

Application start for mainnet and testnet networks takes too much time due to heavy SQL query beign executed:

11:02:17 tx(applyRuntime): BEGIN
11:02:17 tx(applyRuntime): UPDATE "peers" SET "state" = 1, "clock" = NULL WHERE "state" != 0; DELETE FROM mem_accounts2u_delegates; INSERT INTO mem_accounts2u_delegates ("accountId", "dependentId") SELECT "accountId", "dependentId" FROM mem_accounts2delegates; DELETE FROM mem_accounts2u_multisignatures; INSERT INTO mem_accounts2u_multisignatures ("accountId", "dependentId") SELECT "accountId", "dependentId" FROM mem_accounts2multisignatures;
11:02:42 tx(applyRuntime): COMMIT

As you can see it takes 17 seconds to start the application using mainnet network, and the test was performed on very powerfull server. Further details:

                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Delete on mem_accounts2u_delegates  (cost=0.00..11386.22 rows=1106910 width=6) (actual time=1146.561..1146.561 rows=0 loops=1)
   ->  Seq Scan on mem_accounts2u_delegates  (cost=0.00..11386.22 rows=1106910 width=6) (actual time=0.083..329.286 rows=1104770 loops=1)
 Planning time: 0.083 ms
 Execution time: 1146.597 ms
(4 rows)

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Insert on mem_accounts2u_delegates  (cost=0.00..11227.09 rows=1106896 width=86) (actual time=5071.163..5071.163 rows=0 loops=1)
   ->  Seq Scan on mem_accounts2delegates  (cost=0.00..11227.09 rows=1106896 width=86) (actual time=0.019..266.977 rows=1104770 loops=1)
 Planning time: 0.485 ms
 Trigger for constraint mem_accounts2u_delegates_accountId_fkey: time=16757.939 calls=1104770
 Execution time: 21953.207 ms
(5 rows)

Steps to reproduce

N/A

Which version(s) does this affect? (Environment, OS, etc...)

>= 1.0.0

[MaciejBaj]

Also UPDATE "peers" SET "state" = 1, "clock" = NULL WHERE "state" != 0; doesn't have any use case after 1.0.0 as the banning mechanism doesn't rely on timestamps.

[4miners]

@MaciejBaj Good point, also the clock column is not used at all.