feat(database): Add indexes on foreign keys to optimize deletion

Détails

• : Ajoute des index sur les clés étrangères en base de données

Explications

La suppression de lignes est très longue et peut-être lourde lorsqu'on fait une opération en masse. C'est notamment le cas lorsque je voulais supprimer 3097 lignes vides d'un ancien rapport. Plus d'une heure après la suppression n'était toujours pas terminée.

Avec cette optimisation on passe de 5580.504 ms à 19.366 ms pour une suppression de ligne container_promo de l'archive de la CPO réalisé 2022. Maintenant la suppression de toutes les lignes a duré 8 secondes contre plusieurs heures auparavant.

postgres=# EXPLAIN ANALYZE DELETE FROM fields WHERE id IN (10491245);
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Delete on fields  (cost=0.43..8.45 rows=1 width=6) (actual time=0.160..0.162 rows=0 loops=1)
   ->  Index Scan using fields_pkey on fields  (cost=0.43..8.45 rows=1 width=6) (actual time=0.126..0.129 rows=1 loops=1)
         Index Cond: (id = 10491245)
 Planning Time: 0.125 ms
 Trigger for constraint data_versions_field_id_fkey on fields: time=2724.893 calls=27
 Trigger for constraint fields_parent_id_fkey on fields: time=2.030 calls=27
 Trigger for constraint field_id_data_versions_fkey on data_versions: time=2853.294 calls=27
 Execution Time: 5580.504 ms
(8 rows)

à

postgres=# EXPLAIN ANALYZE DELETE FROM fields WHERE id IN (10491246);
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Delete on fields  (cost=0.43..8.45 rows=1 width=6) (actual time=0.124..0.125 rows=0 loops=1)
   ->  Index Scan using fields_pkey on fields  (cost=0.43..8.45 rows=1 width=6) (actual time=0.093..0.096 rows=1 loops=1)
         Index Cond: (id = 10491246)
 Planning Time: 0.177 ms
 Trigger for constraint data_versions_field_id_fkey on fields: time=4.935 calls=27
 Trigger for constraint fields_parent_id_fkey on fields: time=4.908 calls=27
 Trigger for constraint field_id_data_versions_fkey on data_versions: time=9.292 calls=27
 Execution Time: 19.366 ms
(8 rows)

Suppression de toutes les lignes vides du rapport CPO EN réalisé 2022

postgres=# EXPLAIN ANALYZE DELETE FROM fields WHERE id IN (SELECT v.id FROM view_report_10485435 v JOIN da
ta_versions d on v.data_id = d.id WHERE v.field_type = 'container_promo' AND d.value = '');
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on fields  (cost=1444.00..1553.49 rows=13 width=120) (actual time=218.807..218.811 rows=0 loops=1)
   ->  Nested Loop  (cost=1444.00..1553.49 rows=13 width=120) (actual time=210.761..215.932 rows=3095 loops=1)
         ->  HashAggregate  (cost=1443.57..1443.70 rows=13 width=118) (actual time=210.714..211.178 rows=3095 loops=1)
               Group Key: v.id
               Batches: 1  Memory Usage: 649kB
               ->  Nested Loop  (cost=1250.93..1443.54 rows=13 width=118) (actual time=30.408..208.863 rows=3095 loops=1)
                     ->  Subquery Scan on v  (cost=1250.50..1316.83 rows=15 width=116) (actual time=30.337..201.454 rows=3930 loops=1)
                           ->  CTE Scan on view_report_10485435  (cost=1250.50..1316.68 rows=15 width=84) (actual time=30.326..200.202 rows=3930 loops=1)
                                 Filter: ((field_type)::text = 'container_promo'::text)
                                 Rows Removed by Filter: 79312
                                 CTE view_report_10485435
                                   ->  Recursive Union  (cost=0.43..1250.50 rows=2941 width=64) (actual time=0.056..152.417 rows=83242 loops=1)
                                         ->  Index Scan using fields_pkey on fields fields_1  (cost=0.43..8.45 rows=1 width=64) (actual time=0.053..0.055 rows=1 loops=1)
                                               Index Cond: (id = 10485435)
                                         ->  Nested Loop  (cost=0.43..118.32 rows=294 width=64) (actual time=9.709..15.592 rows=10405 loops=8)
                                               ->  WorkTable Scan on view_report_10485435 v_1  (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..1.335 rows=10405 loops=8)
                                               ->  Index Scan using fields_parent_id_index on fields child  (cost=0.43..11.45 rows=29 width=60) (actual time=0.001..0.001 rows=1 loops=83242)
                                                     Index Cond: (parent_id = v_1.id)
                     ->  Index Scan using data_versions_pkey on data_versions d  (cost=0.43..8.45 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=3930)
                           Index Cond: (id = v.data_id)
                           Filter: (value = ''::text)
                           Rows Removed by Filter: 0
         ->  Index Scan using fields_pkey on fields  (cost=0.43..8.45 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=3095)
               Index Cond: (id = v.id)
 Planning Time: 4.366 ms
 Trigger for constraint data_versions_field_id_fkey on fields: time=2131.143 calls=63036
 Trigger for constraint fields_parent_id_fkey on fields: time=1975.009 calls=63036
 Trigger for constraint field_id_data_versions_fkey on data_versions: time=3733.871 calls=63036
 Execution Time: 8076.573 ms
(29 rows)

Captures d'écran

Références

Dépendances