explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usmN

Settings
# exclusive inclusive rows x rows loops node
1. 2,041.300 22,109.935 ↓ 1.2 2,697,840 1

Merge Left Join (cost=981,336.31..1,552,169.08 rows=2,303,618 width=32) (actual time=17,382.331..22,109.935 rows=2,697,840 loops=1)

  • Merge Cond: (d.id_dossier = t_creance.id_dossier)
2. 2,679.448 17,361.115 ↓ 1.2 2,695,523 1

Gather Merge (cost=818,425.36..1,098,856.17 rows=2,303,618 width=106) (actual time=14,705.211..17,361.115 rows=2,695,523 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
3. 170.648 14,681.667 ↑ 1.0 449,254 6 / 6

Merge Left Join (cost=817,425.28..820,443.33 rows=460,724 width=106) (actual time=14,326.511..14,681.667 rows=449,254 loops=6)

  • Merge Cond: (d.id_dossier = tvr.id_dossier)
4. 794.890 12,939.547 ↑ 1.0 449,254 6 / 6

Sort (cost=723,505.58..724,657.39 rows=460,724 width=90) (actual time=12,803.930..12,939.547 rows=449,254 loops=6)

  • Sort Key: d.id_dossier
  • Sort Method: external merge Disk: 29856kB
5. 133.649 12,144.657 ↑ 1.0 449,254 6 / 6

Hash Left Join (cost=485,419.99..656,544.83 rows=460,724 width=90) (actual time=9,331.372..12,144.657 rows=449,254 loops=6)

  • Hash Cond: (d.id_mandat_comercial = t_mandat.id_mandat)
6. 1,716.709 12,010.797 ↑ 1.0 449,254 6 / 6

Hash Join (cost=485,396.98..655,277.30 rows=460,724 width=70) (actual time=9,331.140..12,010.797 rows=449,254 loops=6)

  • Hash Cond: (tsp.id_status_periodicite = d.id_status)
7. 171.447 1,016.115 ↑ 1.0 903,488 6 / 6

Hash Left Join (cost=3,177.01..119,226.40 rows=930,351 width=40) (actual time=51.705..1,016.115 rows=903,488 loops=6)

  • Hash Cond: (m1.id_motif_type = mt1.id_motif_type)
8. 171.992 844.215 ↑ 1.0 903,488 6 / 6

Hash Left Join (cost=3,139.42..116,737.17 rows=930,351 width=49) (actual time=51.232..844.215 rows=903,488 loops=6)

  • Hash Cond: (tsp.id_motif = m1.id_motif)
9. 135.072 668.390 ↑ 1.0 903,488 6 / 6

Hash Left Join (cost=2,787.09..113,942.14 rows=930,351 width=49) (actual time=47.351..668.390 rows=903,488 loops=6)

  • Hash Cond: (tsp.id_motif_mg = mtfmg.id_motif_mandat_gestion)
10. 191.995 486.366 ↑ 1.0 903,488 6 / 6

Hash Join (cost=1.16..107,288.49 rows=930,351 width=58) (actual time=0.092..486.366 rows=903,488 loops=6)

  • Hash Cond: (tsp.id_dossier_status = tds.id_dossier_status)
11. 294.334 294.334 ↑ 1.2 905,732 6 / 6

Parallel Seq Scan on t_status_periodicite tsp (cost=0.00..102,713.10 rows=1,085,410 width=64) (actual time=0.018..294.334 rows=905,732 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
12. 0.006 0.037 ↑ 1.0 6 6 / 6

Hash (cost=1.09..1.09 rows=6 width=26) (actual time=0.036..0.037 rows=6 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.031 0.031 ↑ 1.0 6 6 / 6

Seq Scan on t_dossier_status tds (cost=0.00..1.09 rows=6 width=26) (actual time=0.028..0.031 rows=6 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration) AND ((code_dossier_status)::text <> 'EXCLU'::text))
  • Rows Removed by Filter: 1
14. 9.824 46.952 ↑ 1.0 39,423 6 / 6

Hash (cost=2,293.07..2,293.07 rows=39,429 width=23) (actual time=46.952..46.952 rows=39,423 loops=6)

  • Buckets: 65536 Batches: 1 Memory Usage: 2579kB
15. 9.256 37.128 ↑ 1.0 39,423 6 / 6

Hash Left Join (cost=389.92..2,293.07 rows=39,429 width=23) (actual time=5.313..37.128 rows=39,423 loops=6)

  • Hash Cond: (m2.id_motif_type = mt2.id_motif_type)
16. 10.535 27.343 ↑ 1.0 39,423 6 / 6

Hash Left Join (cost=352.33..2,151.57 rows=39,429 width=32) (actual time=4.766..27.343 rows=39,423 loops=6)

  • Hash Cond: (mtfmg.id_motif = m2.id_motif)
17. 12.118 12.118 ↑ 1.0 39,423 6 / 6

Seq Scan on t_motif_mandat_gestion mtfmg (cost=0.00..1,695.69 rows=39,429 width=32) (actual time=0.025..12.118 rows=39,423 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
  • Rows Removed by Filter: 2046
18. 1.672 4.690 ↑ 1.0 7,875 6 / 6

Hash (cost=253.89..253.89 rows=7,875 width=32) (actual time=4.690..4.690 rows=7,875 loops=6)

  • Buckets: 8192 Batches: 1 Memory Usage: 557kB
19. 3.018 3.018 ↑ 1.0 7,875 6 / 6

Seq Scan on t_motif m2 (cost=0.00..253.89 rows=7,875 width=32) (actual time=0.020..3.018 rows=7,875 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
  • Rows Removed by Filter: 814
20. 0.227 0.529 ↑ 1.0 1,093 6 / 6

Hash (cost=23.93..23.93 rows=1,093 width=23) (actual time=0.529..0.529 rows=1,093 loops=6)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
21. 0.302 0.302 ↑ 1.0 1,093 6 / 6

Seq Scan on t_motif_type mt2 (cost=0.00..23.93 rows=1,093 width=23) (actual time=0.026..0.302 rows=1,093 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
22. 1.608 3.833 ↑ 1.0 7,875 6 / 6

Hash (cost=253.89..253.89 rows=7,875 width=32) (actual time=3.833..3.833 rows=7,875 loops=6)

  • Buckets: 8192 Batches: 1 Memory Usage: 557kB
23. 2.225 2.225 ↑ 1.0 7,875 6 / 6

Seq Scan on t_motif m1 (cost=0.00..253.89 rows=7,875 width=32) (actual time=0.008..2.225 rows=7,875 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
  • Rows Removed by Filter: 814
24. 0.225 0.453 ↑ 1.0 1,093 6 / 6

Hash (cost=23.93..23.93 rows=1,093 width=23) (actual time=0.453..0.453 rows=1,093 loops=6)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
25. 0.228 0.228 ↑ 1.0 1,093 6 / 6

Seq Scan on t_motif_type mt1 (cost=0.00..23.93 rows=1,093 width=23) (actual time=0.009..0.228 rows=1,093 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration))
26. 949.811 9,277.973 ↓ 1.0 2,708,776 6 / 6

Hash (cost=419,754.54..419,754.54 rows=2,687,554 width=62) (actual time=9,277.973..9,277.973 rows=2,708,776 loops=6)

  • Buckets: 262144 Batches: 16 Memory Usage: 17927kB
27. 8,328.162 8,328.162 ↓ 1.0 2,708,776 6 / 6

Seq Scan on t_dossier d (cost=0.00..419,754.54 rows=2,687,554 width=62) (actual time=1.280..8,328.162 rows=2,708,776 loops=6)

28. 0.036 0.211 ↑ 1.0 146 6 / 6

Hash (cost=21.19..21.19 rows=146 width=52) (actual time=0.210..0.211 rows=146 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
29. 0.175 0.175 ↑ 1.0 146 6 / 6

Seq Scan on t_mandat (cost=0.00..21.19 rows=146 width=52) (actual time=0.015..0.175 rows=146 loops=6)

30. 600.075 1,571.472 ↓ 2.9 306,203 6 / 6

Sort (cost=93,919.70..94,185.71 rows=106,404 width=32) (actual time=1,522.570..1,571.472 rows=306,203 loops=6)

  • Sort Key: tvr.id_dossier
  • Sort Method: external sort Disk: 13792kB
31. 35.979 971.397 ↓ 2.9 306,203 6 / 6

Subquery Scan on tvr (cost=82,907.32..85,035.39 rows=106,404 width=32) (actual time=816.598..971.397 rows=306,203 loops=6)

32. 448.131 935.418 ↓ 2.9 306,203 6 / 6

HashAggregate (cost=82,907.32..83,971.35 rows=106,404 width=32) (actual time=816.597..935.418 rows=306,203 loops=6)

  • Group Key: t_versement.id_dossier, t_versement.lib_statut
33. 487.287 487.287 ↑ 1.0 660,903 6 / 6

Seq Scan on t_versement (cost=0.00..77,930.26 rows=663,607 width=40) (actual time=0.047..487.287 rows=660,903 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration) AND ((lib_statut)::text = 'REVERSE'::text))
  • Rows Removed by Filter: 37023
34. 3.365 2,707.520 ↓ 1.0 13,707 1

Materialize (cost=162,910.95..165,209.84 rows=13,622 width=64) (actual time=2,677.090..2,707.520 rows=13,707 loops=1)

35. 15.004 2,704.155 ↓ 1.0 13,707 1

Finalize GroupAggregate (cost=162,910.95..165,039.57 rows=13,622 width=124) (actual time=2,677.085..2,704.155 rows=13,707 loops=1)

  • Group Key: t_creance.id_dossier, t_creance.lib_libelle
36. 19.228 2,689.151 ↑ 1.0 13,711 1

Gather Merge (cost=162,910.95..164,663.04 rows=13,750 width=92) (actual time=2,677.070..2,689.151 rows=13,711 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
37. 2.727 2,669.923 ↑ 1.2 2,285 6 / 6

Partial GroupAggregate (cost=161,910.87..162,007.12 rows=2,750 width=92) (actual time=2,666.976..2,669.923 rows=2,285 loops=6)

  • Group Key: t_creance.id_dossier, t_creance.lib_libelle
38. 1.807 2,667.196 ↑ 1.2 2,287 6 / 6

Sort (cost=161,910.87..161,917.75 rows=2,750 width=50) (actual time=2,666.954..2,667.196 rows=2,287 loops=6)

  • Sort Key: t_creance.id_dossier, t_creance.lib_libelle
  • Sort Method: quicksort Memory: 335kB
39. 2,665.389 2,665.389 ↑ 1.2 2,287 6 / 6

Parallel Seq Scan on t_creance (cost=0.00..161,753.78 rows=2,750 width=50) (actual time=191.483..2,665.389 rows=2,287 loops=6)

  • Filter: ((NOT boo_supprime) AND (NOT boo_en_migration) AND ((lib_libelle)::text = ANY ('{Impayé,Capital}'::text[])))
  • Rows Removed by Filter: 816884
Planning time : 39.954 ms
Execution time : 22,368.928 ms