explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UGOm

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,158.679 ↑ 1.0 1 1

Limit (cost=156,529.17..156,529.18 rows=1 width=4) (actual time=2,158.679..2,158.679 rows=1 loops=1)

2. 0.003 2,158.677 ↑ 1.0 1 1

Aggregate (cost=156,529.17..156,529.18 rows=1 width=4) (actual time=2,158.677..2,158.677 rows=1 loops=1)

3. 0.000 2,158.674 ↓ 0.0 0 1

Nested Loop Left Join (cost=54,048.54..156,529.16 rows=4 width=4) (actual time=2,158.674..2,158.674 rows=0 loops=1)

  • Filter: CASE WHEN (pm.nb_execution <> 0) THEN ((pm.date_debut + ((pm.nb_execution - 1) * pm.periode)) >= date_trunc('day'::text, now())) WHEN (pm.nb_heure_execution <> 0) THEN (pm.date_debut >= date_trunc('day'::text, now())) WHEN ((pm.vecteur_id IS NOT NULL) AND (pm.vecteur_id <> 0) AND (vecteur.nb_execution <> 0)) THEN ((vecteur.date_debut + ((vecteur.nb_execution - 1) * vecteur.periode)) >= date_trunc('day'::text, now())) WHEN ((pm.vecteur_id IS NOT NULL) AND (pm.vecteur_id <> 0) AND (vecteur.nb_heure_execution <> 0)) THEN (vecteur.date_debut >= date_trunc('day'::text, now())) WHEN ((pm.vecteur_id IS NOT NULL) AND (pm.vecteur_id <> 0)) THEN (vecteur.date_fin >= date_trunc('day'::text, now())) ELSE (pm.date_fin >= date_trunc('day'::text, now())) END
4. 0.021 2,158.674 ↓ 0.0 0 1

Nested Loop Left Join (cost=54,048.11..156,519.72 rows=7 width=22) (actual time=2,158.674..2,158.674 rows=0 loops=1)

  • Filter: ((((pc.couleur)::text = '#60FF00'::text) AND (((psc.couleur)::text = ''::text) OR (psc.couleur IS NULL))) OR ((psc.couleur)::text = '#60FF00'::text) OR ((mdc.couleur)::text = '#60FF00'::text) OR ((mprod.couleur)::text = '#60FF00'::text))
  • Rows Removed by Filter: 5
5. 0.111 2,158.638 ↑ 6.6 5 1

Nested Loop Left Join (cost=54,047.83..156,508.87 rows=33 width=39) (actual time=2,116.561..2,158.638 rows=5 loops=1)

  • Join Filter: (psc.id = pt.pmt_sous_classe_id)
  • Rows Removed by Join Filter: 1396
6. 0.018 2,158.377 ↑ 6.6 5 1

Nested Loop Left Join (cost=54,047.83..156,362.77 rows=33 width=42) (actual time=2,116.443..2,158.377 rows=5 loops=1)

  • Join Filter: (pc.id = pt.pmt_classe_id)
  • Rows Removed by Join Filter: 121
7. 0.011 2,158.344 ↑ 6.6 5 1

Nested Loop Left Join (cost=54,047.83..156,349.09 rows=33 width=35) (actual time=2,116.427..2,158.344 rows=5 loops=1)

8. 0.019 2,158.313 ↑ 6.6 5 1

Hash Left Join (cost=54,047.55..156,338.91 rows=33 width=35) (actual time=2,116.420..2,158.313 rows=5 loops=1)

  • Hash Cond: (pm.id = reval.prescription_medic_id)
  • Filter: ((reval.nb IS NULL) OR (reval.nb < 2))
9. 0.011 2,141.627 ↑ 19.6 5 1

Nested Loop Left Join (cost=33,120.16..135,395.46 rows=98 width=35) (actual time=2,099.737..2,141.627 rows=5 loops=1)

10. 283.261 2,141.596 ↑ 19.6 5 1

Hash Join (cost=33,119.88..135,365.79 rows=98 width=38) (actual time=2,099.723..2,141.596 rows=5 loops=1)

  • Hash Cond: (pm.sejour_id = s.id)
  • Join Filter: ((pm.qui = 3761) OR (s.medecin_id = 1328))
  • Rows Removed by Join Filter: 2194
11. 180.004 1,513.527 ↓ 4.7 691,571 1

Hash Left Join (cost=106.11..96,418.34 rows=146,630 width=46) (actual time=0.860..1,513.527 rows=691,571 loops=1)

  • Hash Cond: (pm.id = pmr.prescription_medic_id)
  • Filter: ((pmr.reevaluation = 0) OR (pmr.reevaluation IS NULL) OR (CASE WHEN (pm.date_demarrage IS NULL) THEN ((pm.date_debut + 7) <= date_trunc('day'::text, now())) ELSE ((pm.date_demarrage + 7) <= date_trunc('day'::text, now())) END AND (pmr.reevaluation = 1)))
  • Rows Removed by Filter: 6
12. 1,332.731 1,332.731 ↓ 2.4 691,398 1

Seq Scan on prescription_medic pm (cost=0.00..92,993.18 rows=293,260 width=50) (actual time=0.018..1,332.731 rows=691,398 loops=1)

  • Filter: ((validee > 0) AND ((etat)::text = 'ENCOURS'::text) AND ((type_ordonnance)::text = 'PUI'::text) AND ((date_demarrage + 2) <= date_trunc('day'::text, now())))
  • Rows Removed by Filter: 667412
13. 0.389 0.792 ↓ 1.0 2,639 1

Hash (cost=73.27..73.27 rows=2,627 width=8) (actual time=0.792..0.792 rows=2,639 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 136kB
14. 0.403 0.403 ↓ 1.0 2,639 1

Seq Scan on pm_reevaluation pmr (cost=0.00..73.27 rows=2,627 width=8) (actual time=0.003..0.403 rows=2,639 loops=1)

15. 99.947 344.808 ↑ 1.0 438,950 1

Hash (cost=25,753.01..25,753.01 rows=442,541 width=8) (actual time=344.808..344.808 rows=438,950 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3184kB
16. 244.861 244.861 ↑ 1.0 438,950 1

Seq Scan on sejour s (cost=0.00..25,753.01 rows=442,541 width=8) (actual time=0.005..244.861 rows=438,950 loops=1)

  • Filter: ((etat)::text = ANY ('{PRESENT,RESERVE}'::text[]))
  • Rows Removed by Filter: 374476
17. 0.020 0.020 ↓ 0.0 0 5

Index Scan using dc_idx on dc mdc (cost=0.28..0.29 rows=1 width=5) (actual time=0.004..0.004 rows=0 loops=5)

  • Index Cond: (pm.dc_id = dc_id)
18. 0.011 16.667 ↑ 64.3 20 1

Hash (cost=20,911.32..20,911.32 rows=1,286 width=12) (actual time=16.667..16.667 rows=20 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
19. 0.007 16.656 ↑ 64.3 20 1

Subquery Scan on reval (cost=20,885.60..20,911.32 rows=1,286 width=12) (actual time=16.648..16.656 rows=20 loops=1)

20. 0.025 16.649 ↑ 64.3 20 1

HashAggregate (cost=20,885.60..20,898.46 rows=1,286 width=4) (actual time=16.647..16.649 rows=20 loops=1)

  • Group Key: pmr_1.prescription_medic_id
21. 0.000 16.624 ↑ 53.6 24 1

Nested Loop (cost=0.85..20,879.17 rows=1,286 width=4) (actual time=6.458..16.624 rows=24 loops=1)

22. 0.967 9.430 ↓ 1.0 2,638 1

Nested Loop (cost=0.43..19,543.12 rows=2,627 width=8) (actual time=0.016..9.430 rows=2,638 loops=1)

23. 0.546 0.546 ↓ 1.0 2,639 1

Seq Scan on pm_reevaluation pmr_1 (cost=0.00..79.84 rows=2,627 width=4) (actual time=0.005..0.546 rows=2,639 loops=1)

  • Filter: (reevaluation = 1)
24. 7.917 7.917 ↑ 1.0 1 2,639

Index Scan using prescription_medic_pkey on prescription_medic pm_1 (cost=0.43..7.40 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,639)

  • Index Cond: (id = pmr_1.prescription_medic_id)
25. 7.914 7.914 ↓ 0.0 0 2,638

Index Scan using sejour_pkey on sejour s_1 (cost=0.42..0.50 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2,638)

  • Index Cond: (id = pm_1.sejour_id)
  • Filter: ((etat)::text = ANY ('{PRESENT,RESERVE}'::text[]))
  • Rows Removed by Filter: 1
26. 0.020 0.020 ↑ 1.0 1 5

Index Scan using produit_type_pkey on produit_type pt (cost=0.28..0.30 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (id = CASE WHEN ((pm.remplacant_id IS NOT NULL) AND (pm.remplacant_id <> 0)) THEN pm.remplacant_id ELSE pm.medicament_id END)
27. 0.009 0.015 ↑ 1.0 25 5

Materialize (cost=0.00..1.38 rows=25 width=15) (actual time=0.001..0.003 rows=25 loops=5)

28. 0.006 0.006 ↑ 1.0 25 1

Seq Scan on pmt_classe pc (cost=0.00..1.25 rows=25 width=15) (actual time=0.003..0.006 rows=25 loops=1)

29. 0.100 0.150 ↑ 1.0 280 5

Materialize (cost=0.00..8.20 rows=280 width=5) (actual time=0.002..0.030 rows=280 loops=5)

30. 0.050 0.050 ↑ 1.0 280 1

Seq Scan on pmt_sous_classe psc (cost=0.00..6.80 rows=280 width=5) (actual time=0.003..0.050 rows=280 loops=1)

31. 0.015 0.015 ↓ 0.0 0 5

Index Scan using product_pkey on product mprod (cost=0.28..0.31 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=5)

  • Index Cond: (pm.referentiel_id = referentiel_id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using prescription_medic_pkey on prescription_medic vecteur (cost=0.43..1.26 rows=1 width=18) (never executed)

  • Index Cond: (id = pm.vecteur_id)