Optimisation de la requête d'affichage d'un rapport
Détails
-
API : Optimise la requête pour récupérer les champs d'un rapport -
Front: Corrige la traduction de la chaîne de caractères des filtres -
Front : Ajoute un loader lors du chargement des filtres
Explications
Sans optimisation de la vue matérialisée :
postgres=# EXPLAIN ANALYZE SELECT to_jsonb(sub) AS tree
postgres-# FROM (
postgres(# SELECT
postgres(# v.id,
postgres(# v.name,
postgres(# v.parent_id,
postgres(# v.data_id,
postgres(# v.field_type,
postgres(# v.template_id,
postgres(# v.data,
postgres(# nest_json_field_tree_lazy_loading('view_preprocessed_view_report_5564', v.id, 0) AS children
postgres(# FROM view_preprocessed_view_report_5564 v
postgres(# WHERE v.id = 5564
postgres(# ) sub;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on sub (cost=11194.79..11198.46 rows=13 width=32) (actual time=1550.715..1550.740 rows=1 loops=1)
-> Subquery Scan on v (cost=11194.79..11198.30 rows=13 width=144) (actual time=1550.637..1550.653 rows=1 loops=1)
-> HashAggregate (cost=11194.79..11194.92 rows=13 width=112) (actual time=356.463..356.477 rows=1 loops=1)
Group Key: parents.id, parents.name, parents.parent_id, parents.data_id, parents.field_type, parents.template_id, parents.data
Batches: 1 Memory Usage: 24kB
CTE processed_promos
-> Nested Loop (cost=635.50..673.92 rows=3 width=82) (actual time=215.541..237.694 rows=2688 loops=1)
-> Hash Join (cost=635.21..660.99 rows=3 width=80) (actual time=215.525..233.624 rows=2688 loops=1)
Hash Cond: (view_report_5564_1.id = ct.id)
-> CTE Scan on view_report_5564 view_report_5564_1 (cost=618.21..643.88 rows=6 width=84) (actual time=7.680..24.875 rows=2688 loops=1)
Filter: ((field_type)::text = 'container_promo'::text)
Rows Removed by Filter: 7359
CTE view_report_5564
-> Recursive Union (cost=0.29..618.21 rows=1141 width=62) (actual time=0.032..19.449 rows=10047 loops=1)
-> Index Scan using fields_pkey on fields (cost=0.29..4.31 rows=1 width=62) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (id = 5564)
-> Nested Loop (cost=0.29..59.11 rows=114 width=62) (actual time=0.354..2.119 rows=1256 loops=8)
-> WorkTable Scan on view_report_5564 v_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.121 rows=1256 loops=8)
-> Index Scan using fields_parent_id_index on fields child (cost=0.29..5.75 rows=11 width=58) (actual time=0.001..0.001 rows=1 loops=10047)
Index Cond: (parent_id = v_1.id)
-> Hash (cost=14.50..14.50 rows=200 width=4) (actual time=207.804..207.805 rows=2688 loops=1)
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 127kB
-> HashAggregate (cost=12.50..14.50 rows=200 width=4) (actual time=207.072..207.365 rows=2688 loops=1)
Group Key: ct.id
Batches: 1 Memory Usage: 257kB
-> Function Scan on crosstab ct (cost=0.00..10.00 rows=1000 width=4) (actual time=206.123..206.349 rows=2688 loops=1)
-> Index Scan using data_versions_pkey on data_versions data_versions_1 (cost=0.29..4.31 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=2688)
Index Cond: (id = view_report_5564_1.data_id)
CTE parents
-> Recursive Union (cost=0.00..9824.36 rows=1713 width=120) (actual time=215.545..319.115 rows=18816 loops=1)
-> CTE Scan on processed_promos processed_promos_2 (cost=0.00..0.06 rows=3 width=120) (actual time=215.544..239.173 rows=2688 loops=1)
-> Nested Loop (cost=619.47..979.00 rows=171 width=90) (actual time=1.289..10.598 rows=2304 loops=7)
-> Hash Join (cost=619.18..659.40 rows=171 width=88) (actual time=1.248..7.336 rows=2304 loops=7)
Hash Cond: (view_report_5564_2.id = p.parent_id)
-> CTE Scan on view_report_5564 view_report_5564_2 (cost=618.21..641.03 rows=1141 width=84) (actual time=0.005..6.100 rows=10047 loops=6)
CTE view_report_5564
-> Recursive Union (cost=0.29..618.21 rows=1141 width=62) (actual time=0.023..19.451 rows=10047 loops=1)
-> Index Scan using fields_pkey on fields fields_1 (cost=0.29..4.31 rows=1 width=62) (actual time=0.022..0.022 rows=1 loops=1)
Index Cond: (id = 5564)
-> Nested Loop (cost=0.29..59.11 rows=114 width=62) (actual time=0.352..2.107 rows=1256 loops=8)
-> WorkTable Scan on view_report_5564 v_2 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.134 rows=1256 loops=8)
-> Index Scan using fields_parent_id_index on fields child_1 (cost=0.29..5.75 rows=11 width=58) (actual time=0.001..0.001 rows=1 loops=10047)
Index Cond: (parent_id = v_2.id)
-> Hash (cost=0.60..0.60 rows=30 width=12) (actual time=0.855..0.855 rows=2304 loops=7)
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 148kB
-> WorkTable Scan on parents p (cost=0.00..0.60 rows=30 width=12) (actual time=0.002..0.421 rows=2688 loops=7)
-> Index Scan using data_versions_pkey on data_versions data_versions_2 (cost=0.29..1.87 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=16128)
Index Cond: (id = view_report_5564_2.data_id)
-> Append (cost=38.69..696.29 rows=13 width=112) (actual time=330.898..356.460 rows=1 loops=1)
-> Unique (cost=38.69..38.84 rows=9 width=112) (actual time=330.897..331.728 rows=1 loops=1)
-> Sort (cost=38.69..38.71 rows=9 width=112) (actual time=330.895..331.013 rows=2688 loops=1)
Sort Key: parents.name, parents.parent_id, parents.data_id, parents.field_type, parents.template_id, parents.data
Sort Method: quicksort Memory: 307kB
-> CTE Scan on parents (cost=0.00..38.54 rows=9 width=112) (actual time=318.951..329.515 rows=2688 loops=1)
Filter: (id = 5564)
Rows Removed by Filter: 16128
-> Unique (cost=657.10..657.16 rows=3 width=82) (actual time=24.320..24.324 rows=0 loops=1)
-> Sort (cost=657.10..657.11 rows=3 width=82) (actual time=24.319..24.323 rows=0 loops=1)
Sort Key: children.name, children.parent_id, children.data_id, children.field_type, children.template_id, children.data
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on children (cost=657.04..657.08 rows=3 width=82) (actual time=24.314..24.318 rows=0 loops=1)
-> Sort (cost=657.04..657.05 rows=3 width=90) (actual time=24.314..24.317 rows=0 loops=1)
Sort Key: view_report_5564.name
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=618.60..657.02 rows=3 width=90) (actual time=24.309..24.312 rows=0 loops=1)
-> Hash Join (cost=618.30..644.09 rows=3 width=80) (actual time=24.308..24.311 rows=0 loops=1)
Hash Cond: (view_report_5564.parent_id = processed_promos.id)
-> CTE Scan on view_report_5564 (cost=618.21..643.88 rows=6 width=84) (actual time=0.035..23.381 rows=1 loops=1)
Filter: (id = 5564)
Rows Removed by Filter: 10046
CTE view_report_5564
-> Recursive Union (cost=0.29..618.21 rows=1141 width=62) (actual time=0.033..18.960 rows=10047 loops=1)
-> Index Scan using fields_pkey on fields fields_2 (cost=0.29..4.31 rows=1 width=62) (actual time=0.032..0.033 rows=1 loops=1)
Index Cond: (id = 5564)
-> Nested Loop (cost=0.29..59.11 rows=114 width=62) (actual time=0.363..2.049 rows=1256 loops=8)
-> WorkTable Scan on view_report_5564 v_3 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.120 rows=1256 loops=8)
-> Index Scan using fields_parent_id_index on fields child_2 (cost=0.29..5.75 rows=11 width=58) (actual time=0.001..0.001 rows=1 loops=10047)
Index Cond: (parent_id = v_3.id)
-> Hash (cost=0.06..0.06 rows=3 width=4) (actual time=0.924..0.925 rows=2688 loops=1)
Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 127kB
-> CTE Scan on processed_promos (cost=0.00..0.06 rows=3 width=4) (actual time=0.001..0.487 rows=2688 loops=1)
-> Index Scan using data_versions_pkey on data_versions (cost=0.29..4.31 rows=1 width=6) (never executed)
Index Cond: (id = view_report_5564.data_id)
-> HashAggregate (cost=0.09..0.10 rows=1 width=112) (actual time=0.403..0.404 rows=0 loops=1)
Group Key: processed_promos_1.id, processed_promos_1.name, processed_promos_1.parent_id, processed_promos_1.data_id, processed_promos_1.field_type, processed_promos_1.template_id, processed_promos_1.data
Batches: 1 Memory Usage: 24kB
-> CTE Scan on processed_promos processed_promos_1 (cost=0.00..0.07 rows=1 width=112) (actual time=0.401..0.402 rows=0 loops=1)
Filter: (id = 5564)
Rows Removed by Filter: 2688
Planning Time: 4.085 ms
Execution Time: 1555.497 ms
(91 rows)
Avec l'optimisation de la vue matérialisée :
postgres=# EXPLAIN ANALYZE SELECT to_jsonb(sub) AS tree
postgres-# FROM (
postgres(# SELECT
postgres(# v.id,
postgres(# v.name,
postgres(# v.parent_id,
postgres(# v.data_id,
postgres(# v.field_type,
postgres(# v.template_id,
postgres(# v.data,
postgres(# nest_json_field_tree_lazy_loading('view_preprocessed_view_report_5564_materialized', v.id, 0) AS children
postgres(# FROM view_preprocessed_view_report_5564_materialized v
postgres(# WHERE v.id = 5564
postgres(# ) sub;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on sub (cost=0.00..222.84 rows=1 width=32) (actual time=26.411..27.508 rows=1 loops=1)
-> Seq Scan on view_preprocessed_view_report_5564_materialized v (cost=0.00..222.82 rows=1 width=78) (actual time=26.208..27.270 rows=1 loops=1)
Filter: (id = 5564)
Rows Removed by Filter: 10045
Planning Time: 0.141 ms
Execution Time: 27.568 ms
(6 rows)
Notes
J'ai utilisé les vues matérialisées pour optimiser les performances. Normalement il faut rafraîchir les vues pour qu'elles prennent en compte les modifications mais je constate que ça marche sans. Je vais pousser les changements comme ça, mais il faut rester vigilant sur ce point s'il faut finalement appliquer le rafraîchissement.
J'ai tout de même appliqué un rafraîchissement des vues du rapport concerné à chaque mise à jour d'un champ.
Captures d'écran
Références
https://taches.cemea.org/b/bJJXFZxe6pRHN8uHy/dev-web/E6vZtZqNPJvmtdBbX
Dépendances
Edited by Killian Kemps