explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m0bF

Settings
# exclusive inclusive rows x rows loops node
1. 141.289 1,248.620 ↑ 6.5 563 1

Hash Semi Join (cost=53,624.85..115,377.30 rows=3,645 width=8) (actual time=699.970..1,248.620 rows=563 loops=1)

  • Hash Cond: ((d.centre_id = y.centre_id) AND (b.profrn_statut_reglementaire = z.plan_maitrise_action_statut_reglementaire) AND (c.modele_id = y.modele_id))
  • Join Filter: (CASE WHEN ((b.profrn_statut_reglementaire = 'fabricant'::text) AND (y.plan_maitrise_status_fabricant = 'valide'::text)) THEN true WHEN ((b.profrn_statut_reglementaire =
  • Rows Removed by Join Filter: 6485
  • Buffers: shared hit=133378, temp read=3807 written=3801
2. 5.384 737.434 ↑ 4.2 7,196 1

Nested Loop (cost=51,850.37..104,876.70 rows=30,246 width=36) (actual time=512.207..737.434 rows=7,196 loops=1)

  • Buffers: shared hit=63532, temp read=3807 written=3801
3. 7.369 698.414 ↑ 4.2 8,409 1

Hash Join (cost=51,849.94..84,412.60 rows=35,330 width=36) (actual time=512.191..698.414 rows=8,409 loops=1)

  • Hash Cond: (b.centre_id = d.centre_id)
  • Buffers: shared hit=29786, temp read=3807 written=3801
4. 156.311 691.008 ↓ 1.5 55,987 1

Hash Join (cost=51,847.67..83,919.24 rows=36,743 width=32) (actual time=511.907..691.008 rows=55,987 loops=1)

  • Hash Cond: (a.profrn_id = b.profrn_id)
  • Buffers: shared hit=29785, temp read=3807 written=3801
5. 24.848 30.210 ↓ 1.5 55,987 1

Bitmap Heap Scan on qms_profrn_quality_statut a (cost=891.16..26,609.08 rows=36,743 width=12) (actual time=6.138..30.210 rows=55,987 loops=1)

  • Recheck Cond: (profrn_quality_statut_value = 'presomptionConformite'::text)
  • Filter: profrn_quality_statut_actif
  • Heap Blocks: exact=5128
  • Buffers: shared hit=5407
6. 5.362 5.362 ↑ 1.0 56,007 1

Bitmap Index Scan on i_fk_qms_profrn_quality_statut_value (cost=0.00..881.98 rows=57,273 width=0) (actual time=5.362..5.362 rows=56,007 loops=1)

  • Index Cond: (profrn_quality_statut_value = 'presomptionConformite'::text)
  • Buffers: shared hit=279
7. 252.798 504.487 ↑ 1.0 900,081 1

Hash (cost=33,749.78..33,749.78 rows=937,178 width=24) (actual time=504.487..504.487 rows=900,081 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 14377kB
  • Buffers: shared hit=24378, temp written=3632
8. 251.689 251.689 ↑ 1.0 900,081 1

Seq Scan on qms_profrn b (cost=0.00..33,749.78 rows=937,178 width=24) (actual time=0.012..251.689 rows=900,081 loops=1)

  • Buffers: shared hit=24378
9. 0.014 0.037 ↑ 1.0 50 1

Hash (cost=1.65..1.65 rows=50 width=4) (actual time=0.037..0.037 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
10. 0.023 0.023 ↑ 1.0 50 1

Seq Scan on qms_centre d (cost=0.00..1.65 rows=50 width=4) (actual time=0.014..0.023 rows=50 loops=1)

  • Filter: ((centre_type)::text = 'bu'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
11. 33.636 33.636 ↑ 1.0 1 8,409

Index Scan using pk_qms_produit on qms_produit c (cost=0.42..0.57 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=8,409)

  • Index Cond: (produit_id = b.produit_id)
  • Filter: (NOT produit_top_referencement)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=33746
12. 5.443 22.951 ↑ 1.8 12,995 1

Hash (cost=1,362.86..1,362.86 rows=23,521 width=44) (actual time=22.951..22.951 rows=12,995 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1269kB
  • Buffers: shared hit=2670
13. 4.598 17.508 ↑ 1.8 12,995 1

Hash Join (cost=241.48..1,362.86 rows=23,521 width=44) (actual time=3.188..17.508 rows=12,995 loops=1)

  • Hash Cond: (z.plan_maitrise_id = y.plan_maitrise_id)
  • Buffers: shared hit=2670
14. 9.710 9.772 ↑ 1.8 12,995 1

Nested Loop (cost=0.43..798.40 rows=23,521 width=19) (actual time=0.036..9.772 rows=12,995 loops=1)

  • Buffers: shared hit=2577
  • -> Index Scan using i_fk_qms_plan_maitrise_action_qms_type_interv on qms_plan_maitrise_action z (cost=0.29..58.63 rows=714 width=23) (actual time=0.007..0.592 row
15. 0.009 0.062 ↓ 1.1 13 1

Nested Loop Semi Join (cost=0.14..9.20 rows=12 width=4) (actual time=0.027..0.062 rows=13 loops=1)

  • Join Filter: (v.categ_interv_id = t.categ_interv_id)
  • Rows Removed by Join Filter: 71
  • Buffers: shared hit=3
  • Index Cond: (type_interv_id = v.type_interv_id)
  • Filter: ((NOT plan_maitrise_action_deprecated) AND (plan_maitrise_action_traitement = 'manuel'::text))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=2574
16. 0.020 0.020 ↑ 1.0 33 1

Index Scan using pk_qms_type_interv on qms_type_interv v (cost=0.14..6.63 rows=33 width=8) (actual time=0.006..0.020 rows=33 loops=1)

  • Buffers: shared hit=2
17. 0.026 0.033 ↑ 1.0 3 33

Materialize (cost=0.00..1.09 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=33)

  • Buffers: shared hit=1
18. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on qms_categ_interv_statut_qualite t (cost=0.00..1.07 rows=3 width=4) (actual time=0.006..0.007 rows=3 loops=1)

  • Filter: (quality_status_enum = 'presomptionConformite'::text)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=1
19. 1.595 3.138 ↑ 1.0 6,580 1

Hash (cost=158.80..158.80 rows=6,580 width=33) (actual time=3.138..3.138 rows=6,580 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 495kB
  • Buffers: shared hit=93
20. 1.543 1.543 ↑ 1.0 6,580 1

Seq Scan on qms_plan_maitrise y (cost=0.00..158.80 rows=6,580 width=33) (actual time=0.005..1.543 rows=6,580 loops=1)

  • Buffers: shared hit=93
21.          

SubPlan (forHash Semi Join)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_qms_plan_maitrise_action_profrn_statut on qms_plan_maitrise_action_profrn_statut w_1 (cost=0.42..4.44 rows=1 width=0) (never executed)

  • Index Cond: ((plan_maitrise_action_id = z.plan_maitrise_action_id) AND (profrn_quality_statut_id = a.profrn_quality_statut_id))
  • Heap Fetches: 0
23. 39.122 39.122 ↑ 1.0 278,273 1

Seq Scan on qms_plan_maitrise_action_profrn_statut w_2 (cost=0.00..4,014.73 rows=278,273 width=8) (actual time=0.015..39.122 rows=278,273 loops=1)

  • Buffers: shared hit=1232
24. 15.681 307.824 ↑ 1.0 1 6,996

Nested Loop Semi Join (cost=5.56..15.62 rows=1 width=0) (actual time=0.044..0.044 rows=1 loops=6,996)

  • Buffers: shared hit=65944
25. 13.992 272.844 ↑ 1.0 1 6,996

Bitmap Heap Scan on qms_interv x (cost=5.14..7.15 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=6,996)

  • Recheck Cond: ((profrn_id = a.profrn_id) AND (plan_maitrise_action_id = z.plan_maitrise_action_id))
  • Filter: (interv_etat_avancement <> 6)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=6452
  • Buffers: shared hit=40188
26. 13.236 258.852 ↓ 0.0 0 6,996

BitmapAnd (cost=5.14..5.14 rows=1 width=0) (actual time=0.037..0.037 rows=0 loops=6,996)

  • Buffers: shared hit=33736
27. 13.992 13.992 ↑ 3.0 1 6,996

Bitmap Index Scan on i_fk_qms_interv_qms_profrn (cost=0.00..2.31 rows=3 width=0) (actual time=0.002..0.002 rows=1 loops=6,996)

  • Index Cond: (profrn_id = a.profrn_id)
  • Buffers: shared hit=14032
28. 231.624 231.624 ↓ 11.3 417 6,434

Bitmap Index Scan on i_fk_qms_interv_qms_plan_maitrise_action (cost=0.00..2.57 rows=37 width=0) (actual time=0.036..0.036 rows=417 loops=6,434)

  • Index Cond: (plan_maitrise_action_id = z.plan_maitrise_action_id)
  • Buffers: shared hit=19704
29. 19.299 19.299 ↑ 1.0 1 6,433

Index Only Scan using pk_qms_interv_centre on qms_interv_centre w (cost=0.42..4.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=6,433)

  • Index Cond: ((interv_id = x.interv_id) AND (centre_id = a.centre_id))
  • Heap Fetches: 6433
  • Buffers: shared hit=25756