explain.depesz.com

PostgreSQL's explain analyze made readable

Result: afao

Settings
# exclusive inclusive rows x rows loops node
1. 15.791 540.281 ↓ 14,714.0 14,714 1

Nested Loop Left Join (cost=9,995.32..10,730.53 rows=1 width=634) (actual time=111.080..540.281 rows=14,714 loops=1)

2.          

CTE test

3. 2.677 27.632 ↓ 1.0 6,599 1

Hash Join (cost=2,740.81..7,864.28 rows=6,591 width=55) (actual time=12.371..27.632 rows=6,599 loops=1)

  • Hash Cond: (lf.vendor_id = f.vendor_id)
4. 6.473 14.330 ↓ 1.0 6,599 1

Hash Join (cost=199.30..5,239.55 rows=6,591 width=26) (actual time=1.643..14.330 rows=6,599 loops=1)

  • Hash Cond: (lf.vendor_site_id = slfmi.vendor_site_id)
5. 6.254 6.254 ↓ 1.0 30,848 1

Seq Scan on sagir_lieux_fournisseurs_mo lf (cost=0.00..4,820.23 rows=30,823 width=29) (actual time=0.006..6.254 rows=30,848 loops=1)

6. 0.859 1.603 ↓ 1.0 6,599 1

Hash (cost=116.91..116.91 rows=6,591 width=13) (actual time=1.603..1.603 rows=6,599 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 336kB
7. 0.744 0.744 ↓ 1.0 6,599 1

Seq Scan on sagir_lieux_fournisseurs_mo_informations_supplementaires slfmi (cost=0.00..116.91 rows=6,591 width=13) (actual time=0.005..0.744 rows=6,599 loops=1)

8. 4.562 10.625 ↓ 1.0 23,455 1

Hash (cost=2,248.45..2,248.45 rows=23,445 width=45) (actual time=10.625..10.625 rows=23,455 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2129kB
9. 6.063 6.063 ↓ 1.0 23,455 1

Seq Scan on sagir_fournisseurs_mo f (cost=0.00..2,248.45 rows=23,445 width=45) (actual time=0.007..6.063 rows=23,455 loops=1)

10. 5.031 509.776 ↓ 14,714.0 14,714 1

Nested Loop (cost=2,130.90..2,866.04 rows=1 width=614) (actual time=111.058..509.776 rows=14,714 loops=1)

11. 6.193 490.031 ↓ 14,714.0 14,714 1

Hash Join (cost=2,130.77..2,865.87 rows=1 width=582) (actual time=111.045..490.031 rows=14,714 loops=1)

  • Hash Cond: (spr.role = (r.nom)::text)
12. 5.536 483.584 ↓ 1,337.6 14,714 1

Merge Join (cost=2,106.07..2,841.12 rows=11 width=599) (actual time=110.779..483.584 rows=14,714 loops=1)

  • Merge Cond: (psa.id = spr.id_sagir_psa)
13. 10.663 477.089 ↓ 1,337.6 14,714 1

Nested Loop (cost=2,102.37..2,910.41 rows=11 width=586) (actual time=110.729..477.089 rows=14,714 loops=1)

14. 212.909 451.553 ↓ 1,352.1 14,873 1

Nested Loop (cost=2,102.10..2,907.13 rows=11 width=579) (actual time=110.701..451.553 rows=14,873 loops=1)

  • Join Filter: ((t.psa = psa.psa) AND (pceb.id_sagir_programme = psa.id_sagir_programme))
  • Rows Removed by Join Filter: 1145221
15. 0.120 0.120 ↓ 1.2 78 1

Index Scan using sagir_psa_pk on sagir_psa psa (cost=0.14..13.15 rows=67 width=26) (actual time=0.008..0.120 rows=78 loops=1)

16. 142.191 238.524 ↓ 1,239.4 14,873 78

Materialize (cost=2,101.95..2,879.94 rows=12 width=569) (actual time=1.076..3.058 rows=14,873 loops=78)

17. 2.948 96.333 ↓ 1,239.4 14,873 1

Hash Left Join (cost=2,101.95..2,879.88 rows=12 width=569) (actual time=83.790..96.333 rows=14,873 loops=1)

  • Hash Cond: ((t.vendor_number_creancier = flf_crean.vendor_number) AND (t.vendor_site_code_creancier = flf_crean.vendor_site_code))
18. 8.725 73.645 ↓ 1,239.4 14,873 1

Hash Join (cost=1,871.27..2,629.35 rows=12 width=459) (actual time=64.030..73.645 rows=14,873 loops=1)

  • Hash Cond: ((flf.vendor_number = t.vendor_number) AND (flf.vendor_site_code = t.vendor_site_code))
19. 13.362 13.362 ↓ 1.0 6,599 1

CTE Scan on test flf (cost=0.00..131.82 rows=6,591 width=128) (actual time=12.374..13.362 rows=6,599 loops=1)

20. 9.457 51.558 ↓ 3.3 15,342 1

Hash (cost=1,800.95..1,800.95 rows=4,688 width=353) (actual time=51.558..51.558 rows=15,342 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 3222kB
21. 6.490 42.101 ↓ 3.3 15,342 1

Hash Join (cost=12.70..1,800.95 rows=4,688 width=353) (actual time=0.229..42.101 rows=15,342 loops=1)

  • Hash Cond: ((t.id_entite_budgetaire = pceb.id_entite_budgetaire) AND (t.id_contributeur = pceb.id_contributeur) AND (t.id_programme = p.id))
22. 4.704 35.541 ↓ 1.0 15,342 1

Hash Join (cost=7.38..1,524.24 rows=15,134 width=361) (actual time=0.146..35.541 rows=15,342 loops=1)

  • Hash Cond: (t.id_contributeur = c.id)
23. 4.309 30.828 ↓ 1.0 15,342 1

Hash Join (cost=6.34..1,399.28 rows=15,134 width=325) (actual time=0.129..30.828 rows=15,342 loops=1)

  • Hash Cond: (t.id_entite_budgetaire = eb.id)
24. 4.312 26.512 ↓ 1.0 15,342 1

Hash Join (cost=5.29..1,274.33 rows=15,134 width=289) (actual time=0.106..26.512 rows=15,342 loops=1)

  • Hash Cond: (t.code_retour_cheque = (erc.code)::text)
25. 4.248 22.190 ↓ 1.0 15,342 1

Hash Join (cost=4.25..1,149.37 rows=15,134 width=259) (actual time=0.086..22.190 rows=15,342 loops=1)

  • Hash Cond: (t.code_type_depense = (etd.code)::text)
26. 4.355 17.934 ↓ 1.0 15,342 1

Hash Join (cost=3.18..1,002.01 rows=15,134 width=229) (actual time=0.068..17.934 rows=15,342 loops=1)

  • Hash Cond: (t.code_mode_financement = (emf.code)::text)
27. 4.315 13.570 ↓ 1.0 15,342 1

Hash Join (cost=2.14..877.06 rows=15,134 width=199) (actual time=0.049..13.570 rows=15,342 loops=1)

  • Hash Cond: (t.code_mode_paiement = (emp.code)::text)
28. 7.337 9.245 ↓ 1.0 15,342 1

Hash Join (cost=1.07..729.70 rows=15,134 width=169) (actual time=0.028..9.245 rows=15,342 loops=1)

  • Hash Cond: (t.code_destinataire_paiement = (edp.code)::text)
29. 1.899 1.899 ↓ 1.0 15,342 1

Seq Scan on transactions t (cost=0.00..582.34 rows=15,134 width=142) (actual time=0.006..1.899 rows=15,342 loops=1)

30. 0.004 0.009 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=64) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on enum_destinataire_paiement edp (cost=0.00..1.03 rows=3 width=64) (actual time=0.004..0.005 rows=3 loops=1)

32. 0.006 0.010 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=64) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on enum_mode_paiement emp (cost=0.00..1.03 rows=3 width=64) (actual time=0.004..0.004 rows=3 loops=1)

34. 0.005 0.009 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=64) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on enum_mode_financement emf (cost=0.00..1.02 rows=2 width=64) (actual time=0.004..0.004 rows=2 loops=1)

36. 0.004 0.008 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=64) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on enum_type_depense etd (cost=0.00..1.03 rows=3 width=64) (actual time=0.004..0.004 rows=3 loops=1)

38. 0.006 0.010 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=64) (actual time=0.010..0.010 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on enum_retour_cheque erc (cost=0.00..1.02 rows=2 width=64) (actual time=0.004..0.004 rows=2 loops=1)

40. 0.003 0.007 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.007..0.007 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on entite_budgetaire eb (cost=0.00..1.02 rows=2 width=36) (actual time=0.004..0.004 rows=2 loops=1)

42. 0.003 0.009 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=36) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on contributeur c (cost=0.00..1.02 rows=2 width=36) (actual time=0.006..0.006 rows=2 loops=1)

44. 0.017 0.070 ↓ 1.2 67 1

Hash (cost=4.32..4.32 rows=57 width=28) (actual time=0.070..0.070 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
45. 0.020 0.053 ↓ 1.2 67 1

Hash Join (cost=2.04..4.32 rows=57 width=28) (actual time=0.034..0.053 rows=67 loops=1)

  • Hash Cond: (pceb.id_programme = p.id)
46. 0.014 0.014 ↓ 1.2 67 1

Seq Scan on programme_contributeur_entite_budgetaire pceb (cost=0.00..1.57 rows=57 width=16) (actual time=0.007..0.014 rows=67 loops=1)

47. 0.009 0.019 ↓ 1.1 49 1

Hash (cost=1.46..1.46 rows=46 width=12) (actual time=0.019..0.019 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
48. 0.010 0.010 ↓ 1.1 49 1

Seq Scan on programme p (cost=0.00..1.46 rows=46 width=12) (actual time=0.005..0.010 rows=49 loops=1)

49. 1.812 19.740 ↓ 1.0 6,599 1

Hash (cost=131.82..131.82 rows=6,591 width=128) (actual time=19.740..19.740 rows=6,599 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 672kB
50. 17.928 17.928 ↓ 1.0 6,599 1

CTE Scan on test flf_crean (cost=0.00..131.82 rows=6,591 width=128) (actual time=0.001..17.928 rows=6,599 loops=1)

51. 14.873 14.873 ↑ 1.0 1 14,873

Index Scan using lot_transaction_pk on lot_transaction lt (cost=0.28..0.30 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=14,873)

  • Index Cond: (id = t.id_lot_transaction)
  • Filter: (code_statut_lot <> 'ERRRECPT'::text)
  • Rows Removed by Filter: 0
52. 0.943 0.959 ↓ 219.8 14,728 1

Sort (cost=3.70..3.87 rows=67 width=21) (actual time=0.045..0.959 rows=14,728 loops=1)

  • Sort Key: spr.id_sagir_psa
  • Sort Method: quicksort Memory: 31kB
53. 0.016 0.016 ↓ 1.2 78 1

Seq Scan on sagir_psa_role spr (cost=0.00..1.67 rows=67 width=21) (actual time=0.008..0.016 rows=78 loops=1)

54. 0.016 0.254 ↓ 21.5 43 1

Hash (cost=24.67..24.67 rows=2 width=15) (actual time=0.254..0.254 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
55. 0.028 0.238 ↓ 21.5 43 1

Nested Loop (cost=4.88..24.67 rows=2 width=15) (actual time=0.068..0.238 rows=43 loops=1)

56. 0.026 0.167 ↓ 21.5 43 1

Nested Loop (cost=4.74..24.24 rows=2 width=19) (actual time=0.055..0.167 rows=43 loops=1)

57. 0.008 0.098 ↓ 21.5 43 1

Nested Loop (cost=4.60..23.90 rows=2 width=4) (actual time=0.049..0.098 rows=43 loops=1)

58. 0.025 0.025 ↑ 1.0 1 1

Index Scan using utilisateur_lower_login_idx on utilisateur u (cost=0.29..8.30 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: (lower(login) = lower(("current_user"())::text))
59. 0.053 0.065 ↓ 10.8 43 1

Bitmap Heap Scan on utilisateur_role ur (cost=4.31..15.56 rows=4 width=8) (actual time=0.023..0.065 rows=43 loops=1)

  • Recheck Cond: (utilisateur = u.id)
  • Heap Blocks: exact=6
60. 0.012 0.012 ↓ 10.8 43 1

Bitmap Index Scan on utilisateur_role_pkey (cost=0.00..4.31 rows=4 width=0) (actual time=0.012..0.012 rows=43 loops=1)

  • Index Cond: (utilisateur = u.id)
61. 0.043 0.043 ↑ 1.0 1 43

Index Scan using role_pkey on role r (cost=0.14..0.17 rows=1 width=23) (actual time=0.001..0.001 rows=1 loops=43)

  • Index Cond: (id = ur.role)
62. 0.043 0.043 ↑ 1.0 1 43

Index Only Scan using application_pkey on application a (cost=0.14..0.21 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=43)

  • Index Cond: (id = r.application)
  • Heap Fetches: 43
63. 14.714 14.714 ↑ 1.0 1 14,714

Index Scan using enum_statut_lot_pk on enum_statut_lot etl (cost=0.13..0.17 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=14,714)

  • Index Cond: ((code)::text = lt.code_statut_lot)
64. 14.714 14.714 ↑ 1.0 1 14,714

Index Scan using fichier_transfert_pk on fichier_transfert ft (cost=0.14..0.16 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=14,714)

  • Index Cond: (id = t.id_fichier_transfert)
Planning time : 10.156 ms
Execution time : 542.256 ms