explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M49o

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 122.605 1,676.105 ↑ 6.0 564 1

Hash Semi Join (cost=47,381.66..135,113.55 rows=3,381 width=8) (actual time=865.991..1,676.105 rows=564 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: 6484
  • Buffers: shared hit=148754, temp read=3642 written=3636
2. 7.767 1,178.405 ↑ 3.9 7,196 1

Nested Loop (cost=45,607.18..125,257.73 rows=27,734 width=36) (actual time=568.655..1,178.405 rows=7,196 loops=1)

  • Buffers: shared hit=78913, temp read=3642 written=3636
3. 7.267 1,120.472 ↑ 3.9 8,361 1

Hash Join (cost=45,606.75..106,064.04 rows=32,521 width=36) (actual time=568.569..1,120.472 rows=8,361 loops=1)

  • Hash Cond: (b.centre_id = d.centre_id)
  • Buffers: shared hit=45359, temp read=3642 written=3636
4. 129.034 1,113.163 ↓ 1.7 55,939 1

Hash Join (cost=45,604.48..105,609.72 rows=33,822 width=32) (actual time=564.527..1,113.163 rows=55,939 loops=1)

  • Hash Cond: (a.profrn_id = b.profrn_id)
  • Buffers: shared hit=45358, temp read=3642 written=3636
5. 424.320 424.320 ↓ 1.7 55,939 1

Seq Scan on qms_profrn_quality_statut a (cost=0.00..54,170.01 rows=33,822 width=12) (actual time=3.538..424.320 rows=55,939 loops=1)

  • Filter: (profrn_quality_statut_actif AND (profrn_quality_statut_value = 'presomptionConformite'::text))
  • Rows Removed by Filter: 2345760
  • Buffers: shared hit=24149
6. 246.201 559.809 ↑ 1.0 860,095 1

Hash (cost=29,810.99..29,810.99 rows=860,199 width=24) (actual time=559.809..559.809 rows=860,095 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 13823kB
  • Buffers: shared hit=21209, temp written=3468
7. 313.608 313.608 ↑ 1.0 860,095 1

Seq Scan on qms_profrn b (cost=0.00..29,810.99 rows=860,199 width=24) (actual time=0.030..313.608 rows=860,095 loops=1)

  • Buffers: shared hit=21209
8. 0.016 0.042 ↑ 1.0 50 1

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

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

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

  • Filter: ((centre_type)::text = 'bu'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
10. 50.166 50.166 ↑ 1.0 1 8,361

Index Scan using pk_qms_produit on qms_produit c (cost=0.42..0.58 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=8,361)

  • Index Cond: (produit_id = b.produit_id)
  • Filter: (NOT produit_top_referencement)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=33554
11. 5.349 26.257 ↑ 1.8 12,993 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1269kB
  • Buffers: shared hit=2669
12. 4.570 20.908 ↑ 1.8 12,993 1

Hash Join (cost=241.48..1,362.86 rows=23,521 width=44) (actual time=3.766..20.908 rows=12,993 loops=1)

  • Hash Cond: (z.plan_maitrise_id = y.plan_maitrise_id)
  • Buffers: shared hit=2669
13. 12.607 12.710 ↑ 1.8 12,993 1

Nested Loop (cost=0.43..798.40 rows=23,521 width=19) (actual time=0.099..12.710 rows=12,993 loops=1)

  • Buffers: shared hit=2576
  • -> 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.009..0.804 row
14. 0.023 0.103 ↓ 1.1 13 1

Nested Loop Semi Join (cost=0.14..9.20 rows=12 width=4) (actual time=0.065..0.103 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=2573
15. 0.047 0.047 ↑ 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.025..0.047 rows=33 loops=1)

  • Buffers: shared hit=2
16. 0.022 0.033 ↑ 1.0 3 33

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

  • Buffers: shared hit=1
17. 0.011 0.011 ↑ 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.010..0.011 rows=3 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 495kB
  • Buffers: shared hit=93
19. 1.984 1.984 ↑ 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.013..1.984 rows=6,580 loops=1)

  • Buffers: shared hit=93
20.          

SubPlan (forHash Semi Join)

21. 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
22. 41.014 41.014 ↑ 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.030..41.014 rows=278,273 loops=1)

  • Buffers: shared hit=1232
23. 16.248 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=65937
24. 20.988 265.848 ↑ 1.0 1 6,996

Bitmap Heap Scan on qms_interv x (cost=5.14..7.15 rows=1 width=4) (actual time=0.038..0.038 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=6451
  • Buffers: shared hit=40185
25. 5.713 244.860 ↓ 0.0 0 6,996

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

  • Buffers: shared hit=33734
26. 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
27. 225.155 225.155 ↓ 11.2 416 6,433

Bitmap Index Scan on i_fk_qms_interv_qms_plan_maitrise_action (cost=0.00..2.57 rows=37 width=0) (actual time=0.035..0.035 rows=416 loops=6,433)

  • Index Cond: (plan_maitrise_action_id = z.plan_maitrise_action_id)
  • Buffers: shared hit=19702
28. 25.728 25.728 ↑ 1.0 1 6,432

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.004..0.004 rows=1 loops=6,432)

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