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