The update_full_account_codes trigger can get slow

[PetrDlouhy]

I have about 13000 hordak accounts in my system (2 for every user account), and creating new accounts are starting to be very slow (~7 seconds). It is caused by the update_full_account_codes trigger, which take about 3 seconds.

[PetrDlouhy]

Here is output from PostgreSQL explain analyze:

explain analyze UPDATE
                    hordak_account AS a
                SET
                    full_code = (
                        SELECT string_agg(code, '' order by lft)
                        FROM hordak_account AS a2
                        WHERE a2.lft <= a.lft AND a2.rght >= a.rght AND a.tree_id = a2.tree_id
                    );

                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on hordak_account a  (cost=0.00..57994.62 rows=11320 width=344) (actual time=833.244..833.244 rows=0 loops=1)
   ->  Seq Scan on hordak_account a  (cost=0.00..57994.62 rows=11320 width=344) (actual time=0.377..172.026 rows=11320 loops=1)
         SubPlan 1
           ->  Aggregate  (cost=4.09..4.10 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=11320)
                 ->  Index Scan using hordak_account_tree_id_777f166b on hordak_account a2  (cost=0.08..4.09 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=11320)
                       Index Cond: (a.tree_id = tree_id)
                       Filter: ((lft <= a.lft) AND (rght >= a.rght))
 Planning time: 0.446 ms
 Trigger check_account_type_trigger: time=94.905 calls=11320
 Trigger update_full_account_codes_trigger: time=2183.384 calls=1
 Execution time: 3016.718 ms

[adamcharnock]

To what extent is this still a problem @PetrDlouhy?

[PetrDlouhy]

@adamcharnock With #49 the problem disappeared for me, because I use large number of accounts, but only few of them have code set. I expect, that if somebody has large number of accounts with codes it would be a problem.

EDIT: I realized, that the trigger is disabled on my application, so I am not sure if it wouldn't be a problem if I enabled it in current state.

[adamcharnock]

Ok, thank you @PetrDlouhy! I think #117 should now really sort this out.