explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DO7q

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 2,056.581 ↑ 1.0 1 1

Aggregate (cost=219,742.07..219,742.08 rows=1 width=8) (actual time=2,056.581..2,056.581 rows=1 loops=1)

  • Buffers: shared hit=204800 dirtied=1, temp read=3642 written=3636
2. 7.049 2,056.553 ↓ 0.0 0 1

Hash Semi Join (cost=196,750.59..219,741.97 rows=43 width=4) (actual time=2,056.553..2,056.553 rows=0 loops=1)

  • Hash Cond: (pf.profrn_id = a.profrn_id)
  • Buffers: shared hit=204800 dirtied=1, temp read=3642 written=3636
3. 18.601 464.806 ↓ 6.8 74,012 1

Hash Join (cost=61,594.78..84,555.52 rows=10,853 width=4) (actual time=379.975..464.806 rows=74,012 loops=1)

  • Hash Cond: (pf.fourn_id = fourn.fourn_id)
  • Buffers: shared hit=56048
4. 34.148 436.604 ↓ 6.8 74,012 1

Hash Join (cost=60,316.26..83,127.78 rows=10,853 width=8) (actual time=370.227..436.604 rows=74,012 loops=1)

  • Hash Cond: (pf.produit_id = p.produit_id)
  • Buffers: shared hit=55835
5. 33.615 40.098 ↓ 1.0 74,013 1

Bitmap Heap Scan on qms_profrn pf (cost=1,156.78..23,583.28 rows=73,730 width=12) (actual time=7.250..40.098 rows=74,013 loops=1)

  • Recheck Cond: (centre_id = 16)
  • Filter: (profrn_statut_reglementaire = ANY ('{fabricant,importateur,distributeur}'::text[]))
  • Heap Blocks: exact=5300
  • Buffers: shared hit=5510
6. 6.483 6.483 ↑ 1.0 74,051 1

Bitmap Index Scan on i_fk_qms_profrn_qms_centre (cost=0.00..1,138.35 rows=74,923 width=0) (actual time=6.483..6.483 rows=74,051 loops=1)

  • Index Cond: (centre_id = 16)
  • Buffers: shared hit=210
7. 31.628 362.358 ↑ 1.0 109,164 1

Hash (cost=57,786.56..57,786.56 rows=109,834 width=12) (actual time=362.358..362.358 rows=109,164 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5447kB
  • Buffers: shared hit=50325
8. 330.730 330.730 ↑ 1.0 109,164 1

Seq Scan on qms_produit p (cost=0.00..57,786.56 rows=109,834 width=12) (actual time=0.016..330.730 rows=109,164 loops=1)

  • Filter: (produit_top_referencement IS TRUE)
  • Rows Removed by Filter: 636486
  • Buffers: shared hit=50325
9. 5.282 9.601 ↑ 1.0 31,065 1

Hash (cost=890.20..890.20 rows=31,065 width=4) (actual time=9.601..9.601 rows=31,065 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1349kB
  • Buffers: shared hit=213
10. 4.319 4.319 ↑ 1.0 31,065 1

Index Only Scan using pk_qms_fourn on qms_fourn fourn (cost=0.29..890.20 rows=31,065 width=4) (actual time=0.043..4.319 rows=31,065 loops=1)

  • Heap Fetches: 31
  • Buffers: shared hit=213
11. 0.299 1,584.698 ↑ 6.0 564 1

Hash (cost=135,113.55..135,113.55 rows=3,381 width=8) (actual time=1,584.698..1,584.698 rows=564 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 55kB
  • Buffers: shared hit=148752 dirtied=1, temp read=3642 written=3636
12. 133.171 1,584.399 ↑ 6.0 564 1

Hash Semi Join (cost=47,381.66..135,113.55 rows=3,381 width=8) (actual time=789.290..1,584.399 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_stat
  • Rows Removed by Join Filter: 6484
  • Buffers: shared hit=148752 dirtied=1, temp read=3642 written=3636
  • -> 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 execute
  • Index Cond: ((plan_maitrise_action_id = z.plan_maitrise_action_id) AND (profrn_quality_statut_id = a.profrn_quality_statut_id))
  • Heap Fetches: 0
13. 6.428 1,071.016 ↑ 3.9 7,196 1

Nested Loop (cost=45,607.18..125,257.73 rows=27,734 width=40) (actual time=473.102..1,071.016 rows=7,196 loops=1)

  • Buffers: shared hit=78913, temp read=3642 written=3636
14. 7.342 1,031.144 ↑ 3.9 8,361 1

Hash Join (cost=45,606.75..106,064.04 rows=32,521 width=40) (actual time=473.076..1,031.144 rows=8,361 loops=1)

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

Hash Join (cost=45,604.48..105,609.72 rows=33,822 width=36) (actual time=469.483..1,023.758 rows=55,939 loops=1)

  • Hash Cond: (a.profrn_id = b.profrn_id)
  • Buffers: shared hit=45358, temp read=3642 written=3636
16. 426.154 426.154 ↓ 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.006..426.154 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
17. 231.764 465.166 ↑ 1.0 860,095 1

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

  • Buckets: 262144 Batches: 4 Memory Usage: 13823kB
  • Buffers: shared hit=21209, temp written=3468
18. 233.402 233.402 ↑ 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.010..233.402 rows=860,095 loops=1)

  • Buffers: shared hit=21209
19. 0.015 0.044 ↑ 1.0 50 1

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

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

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

  • Filter: ((centre_type)::text = 'bu'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
21. 33.444 33.444 ↑ 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.004..0.004 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
22. 5.542 26.471 ↑ 1.8 12,993 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1269kB
  • Buffers: shared hit=2670 dirtied=1
23. 5.026 20.929 ↑ 1.8 12,993 1

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

  • Hash Cond: (z.plan_maitrise_id = y.plan_maitrise_id)
  • Buffers: shared hit=2670 dirtied=1
24. 12.019 12.107 ↑ 1.8 12,993 1

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

  • Buffers: shared hit=2577 dirtied=1
  • -> 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 tim
25. 0.023 0.088 ↓ 1.1 13 1

Nested Loop Semi Join (cost=0.14..9.20 rows=12 width=4) (actual time=0.051..0.088 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 dirtied=1
26. 0.032 0.032 ↑ 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.019..0.032 rows=33 loops=1)

  • Buffers: shared hit=2
27. 0.019 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
28. 0.014 0.014 ↑ 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.013..0.014 rows=3 loops=1)

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

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

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

  • Buffers: shared hit=93
31.          

SubPlan (forHash Semi Join)

32. 45.917 45.917 ↑ 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.027..45.917 rows=278,273 loops=1)

  • Buffers: shared hit=1232
33. 9.252 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
34. 20.988 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=6451
  • Buffers: shared hit=40185
35. 237.864 251.856 ↓ 0.0 0 6,996

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

  • Buffers: shared hit=33734
  • -> 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=416 loops=64
36. 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
  • Index Cond: (plan_maitrise_action_id = z.plan_maitrise_action_id)
  • Buffers: shared hit=19702
37. 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