explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UoIA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 72,073.595 ↑ 1.0 1 1

Aggregate (cost=45,503.57..45,503.58 rows=1 width=4) (actual time=72,073.595..72,073.595 rows=1 loops=1)

2.          

Initplan (for Aggregate)

3. 0.017 0.116 ↑ 1.0 1 1

Aggregate (cost=3.89..3.90 rows=1 width=32) (actual time=0.115..0.116 rows=1 loops=1)

4. 0.099 0.099 ↑ 1.0 16 1

Seq Scan on statut_prestation (cost=0.00..3.85 rows=16 width=4) (actual time=0.017..0.099 rows=16 loops=1)

  • Filter: ((spn_groupe_mestachesv3)::text = 'ATT_REP_PROD'::text)
  • Rows Removed by Filter: 52
5. 0.024 72,073.562 ↓ 8.0 8 1

Nested Loop (cost=5,646.94..45,499.66 rows=1 width=4) (actual time=2,659.467..72,073.562 rows=8 loops=1)

6. 0.016 72,073.514 ↓ 8.0 8 1

Nested Loop (cost=5,646.80..45,499.50 rows=1 width=8) (actual time=2,659.459..72,073.514 rows=8 loops=1)

7. 0.137 72,073.426 ↓ 8.0 8 1

Nested Loop (cost=5,646.38..45,498.67 rows=1 width=12) (actual time=2,659.445..72,073.426 rows=8 loops=1)

  • Join Filter: ((dpp.dop_id = dop.dop_id) AND (dos.dos_id = dop.dos_id))
  • Rows Removed by Join Filter: 82
8. 0.126 72,072.535 ↓ 9.7 58 1

Merge Join (cost=5,645.96..45,492.44 rows=6 width=20) (actual time=389.216..72,072.535 rows=58 loops=1)

  • Merge Cond: (dpp.spn_id = spn.spn_id)
9. 42,555.554 72,072.304 ↓ 9.7 58 1

Nested Loop (cost=5,640.21..244,716.97 rows=6 width=24) (actual time=389.149..72,072.304 rows=58 loops=1)

  • Join Filter: (dps.dpn_id = dpp.dpn_id)
  • Rows Removed by Join Filter: 205409300
10. 53.162 53.162 ↑ 12.2 12,474 1

Index Scan using dossier_participant_prestation_spn_id_fkey on dossier_participant_prestation dpp (cost=0.42..158,980.37 rows=152,511 width=12) (actual time=0.169..53.162 rows=12,474 loops=1)

  • Index Cond: (spn_id = ANY ($0))
  • Filter: (dpp_etat = ANY ('{1,P,C}'::bpchar[]))
  • Rows Removed by Filter: 635
11. 29,103.897 29,463.588 ↓ 1,176.2 16,467 12,474

Materialize (cost=5,639.78..53,709.32 rows=14 width=24) (actual time=0.007..2.362 rows=16,467 loops=12,474)

12. 16.710 359.691 ↓ 1,176.2 16,467 1

Nested Loop (cost=5,639.78..53,709.25 rows=14 width=24) (actual time=89.917..359.691 rows=16,467 loops=1)

13. 14.344 208.659 ↓ 10.9 22,387 1

Nested Loop (cost=5,639.35..50,525.79 rows=2,053 width=20) (actual time=89.893..208.659 rows=22,387 loops=1)

14. 26.917 115.119 ↓ 1.5 6,092 1

Bitmap Heap Scan on dossier dos (cost=5,638.93..26,078.59 rows=4,026 width=12) (actual time=89.841..115.119 rows=6,092 loops=1)

  • Recheck Cond: ((per_id_vendeur = 898418) AND (sta_id <> ALL ('{4,5}'::integer[])))
  • Rows Removed by Index Recheck: 2579
  • Filter: ((dos_dossier_guide IS FALSE) AND (dos_etat = '1'::bpchar))
  • Rows Removed by Filter: 114
  • Heap Blocks: exact=8052
15. 2.654 88.202 ↓ 0.0 0 1

BitmapAnd (cost=5,638.93..5,638.93 rows=7,011 width=0) (actual time=88.202..88.202 rows=0 loops=1)

16. 4.607 4.607 ↓ 1.0 12,701 1

Bitmap Index Scan on dossier_per_id_vendeur_fkey (cost=0.00..238.97 rows=12,606 width=0) (actual time=4.607..4.607 rows=12,701 loops=1)

  • Index Cond: (per_id_vendeur = 898418)
17. 80.941 80.941 ↓ 1.0 339,651 1

Bitmap Index Scan on idx_dossier_not_annule (cost=0.00..5,397.69 rows=337,054 width=0) (actual time=80.941..80.941 rows=339,651 loops=1)

18. 79.196 79.196 ↓ 4.0 4 6,092

Index Scan using depart_prestation_statut_dos_id_fkey on depart_prestation_statut dps (cost=0.42..6.06 rows=1 width=8) (actual time=0.007..0.013 rows=4 loops=6,092)

  • Index Cond: (dos_id = dos.dos_id)
  • Filter: ((dps_date_modification)::date < ((('now'::cstring)::date - '2 days'::interval))::date)
  • Rows Removed by Filter: 0
19. 134.322 134.322 ↑ 1.0 1 22,387

Index Scan using depart_prestation_pkey on depart_prestation dpn (cost=0.43..1.54 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=22,387)

  • Index Cond: (dpn_id = dps.dpn_id)
  • Filter: (COALESCE(dpn_service_traitement, ''::bpchar) = ANY ('{A,T}'::bpchar[]))
  • Rows Removed by Filter: 0
20. 0.077 0.105 ↓ 1.4 97 1

Sort (cost=5.75..5.92 rows=68 width=4) (actual time=0.063..0.105 rows=97 loops=1)

  • Sort Key: spn.spn_id
  • Sort Method: quicksort Memory: 28kB
21. 0.028 0.028 ↑ 1.0 68 1

Seq Scan on statut_prestation spn (cost=0.00..3.68 rows=68 width=4) (actual time=0.007..0.028 rows=68 loops=1)

22. 0.754 0.754 ↓ 2.0 2 58

Index Scan using dossier_participant_dos_id_fkey on dossier_participant dop (cost=0.42..1.02 rows=1 width=8) (actual time=0.010..0.013 rows=2 loops=58)

  • Index Cond: (dos_id = dps.dos_id)
  • Filter: (dop_etat = '1'::bpchar)
  • Rows Removed by Filter: 0
23. 0.072 0.072 ↑ 1.0 1 8

Index Only Scan using denorm_depart_dep_id_fkey on denorm_depart dep (cost=0.42..0.81 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=8)

  • Index Cond: (dep_id = dos.dep_id)
  • Heap Fetches: 3
24. 0.024 0.024 ↑ 1.0 1 8

Index Only Scan using statut_dossier_pkey on statut_dossier sta (cost=0.14..0.15 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (sta_id = dos.sta_id)
  • Heap Fetches: 0
Planning time : 13.500 ms
Execution time : 72,074.442 ms