explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rFOA : Optimization for: plan #M49o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 114.947 1,124.415 ↑ 6.3 564 1

Hash Semi Join (cost=48,235.85..108,038.71 rows=3,548 width=8) (actual time=631.942..1,124.415 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=129664 read=279, temp read=3643 written=3637
2. 3.684 660.190 ↑ 4.0 7,196 1

Nested Loop (cost=46,461.37..97,789.06 rows=29,101 width=36) (actual time=474.453..660.190 rows=7,196 loops=1)

  • Buffers: shared hit=59826 read=279, temp read=3643 written=3637
3. 6.884 623.062 ↑ 4.1 8,361 1

Hash Join (cost=46,460.95..77,649.29 rows=34,124 width=36) (actual time=474.433..623.062 rows=8,361 loops=1)

  • Hash Cond: (b.centre_id = d.centre_id)
  • Buffers: shared hit=26272 read=279, temp read=3643 written=3637
4. 122.574 616.144 ↓ 1.6 55,939 1

Hash Join (cost=46,458.67..77,172.69 rows=35,489 width=32) (actual time=474.209..616.144 rows=55,939 loops=1)

  • Hash Cond: (a.profrn_id = b.profrn_id)
  • Buffers: shared hit=26271 read=279, temp read=3643 written=3637
5. 20.317 27.300 ↓ 1.6 55,939 1

Bitmap Heap Scan on qms_profrn_quality_statut a (cost=854.19..25,694.68 rows=35,489 width=12) (actual time=7.687..27.300 rows=55,939 loops=1)

  • Recheck Cond: (profrn_quality_statut_value = 'presomptionConformite'::text)
  • Filter: profrn_quality_statut_actif
  • Heap Blocks: exact=5062
  • Buffers: shared hit=5062 read=279
6. 6.983 6.983 ↓ 1.0 55,939 1

Bitmap Index Scan on i_fk_qms_profrn_quality_statut_value (cost=0.00..845.32 rows=55,319 width=0) (actual time=6.983..6.983 rows=55,939 loops=1)

  • Index Cond: (profrn_quality_statut_value = 'presomptionConformite'::text)
  • Buffers: shared read=279
7. 226.704 466.270 ↑ 1.0 860,095 1

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

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

  • Buffers: shared hit=21209
9. 0.009 0.034 ↑ 1.0 50 1

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

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

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

  • Filter: ((centre_type)::text = 'bu'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
11. 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
12. 4.322 20.645 ↑ 1.8 12,993 1

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

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

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

  • Hash Cond: (z.plan_maitrise_id = y.plan_maitrise_id)
  • Buffers: shared hit=2669
14. 8.714 8.777 ↑ 1.8 12,993 1

Nested Loop (cost=0.43..798.40 rows=23,521 width=19) (actual time=0.045..8.777 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.007..0.520 row
15. 0.007 0.063 ↓ 1.1 13 1

Nested Loop Semi Join (cost=0.14..9.20 rows=12 width=4) (actual time=0.029..0.063 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
16. 0.023 0.023 ↑ 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.007..0.023 rows=33 loops=1)

  • Buffers: shared hit=2
17. 0.025 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.008 0.008 ↑ 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.007..0.008 rows=3 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 495kB
  • Buffers: shared hit=93
20. 1.427 1.427 ↑ 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.007..1.427 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. 34.801 34.801 ↑ 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.023..34.801 rows=278,273 loops=1)

  • Buffers: shared hit=1232
24. 15.684 293.832 ↑ 1.0 1 6,996

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

  • Buffers: shared hit=65937
25. 13.992 258.852 ↑ 1.0 1 6,996

Bitmap Heap Scan on qms_interv x (cost=5.14..7.15 rows=1 width=4) (actual time=0.037..0.037 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
26. 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
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. 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
29. 19.296 19.296 ↑ 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.003..0.003 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