explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txMw : Optimization for: Optimization for: Optimization for: Optimization for: plan #iqEt; plan #SBbB; plan #awNG; plan #9Tlt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 896.148 6,037.137 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=3.41..6,424,278.48 rows=216,604 width=925) (actual time=0.251..6,037.137 rows=200,302 loops=1)

  • Join Filter: (ou.id_outlier = a.id_outlier)
  • Rows Removed by Join Filter: 2539597
  • Buffers: shared hit=3707707 dirtied=15
2. 177.195 4,940.629 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=3.41..5,817,961.60 rows=216,604 width=906) (actual time=0.208..4,940.629 rows=200,302 loops=1)

  • Buffers: shared hit=3707706 dirtied=15
3. 24.964 4,763.434 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=3.27..5,783,791.26 rows=216,604 width=895) (actual time=0.204..4,763.434 rows=200,302 loops=1)

  • Buffers: shared hit=3676588 dirtied=15
4. 73.371 4,538.168 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.99..5,718,737.41 rows=216,604 width=847) (actual time=0.200..4,538.168 rows=200,302 loops=1)

  • Buffers: shared hit=3629911 dirtied=15
5. 134.165 3,863.891 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.56..5,621,543.09 rows=216,604 width=839) (actual time=0.184..3,863.891 rows=200,302 loops=1)

  • Buffers: shared hit=2850612 dirtied=15
6. 122.395 3,329.122 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.14..5,525,170.93 rows=216,604 width=781) (actual time=0.180..3,329.122 rows=200,302 loops=1)

  • Join Filter: (b.id_boite = a.id_boite)
  • Rows Removed by Join Filter: 121407
  • Buffers: shared hit=2543675 dirtied=15
7. 129.502 3,206.727 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.14..5,519,738.48 rows=216,604 width=771) (actual time=0.172..3,206.727 rows=200,302 loops=1)

  • Join Filter: (e.id_energie = a.id_energie)
  • Rows Removed by Join Filter: 223504
  • Buffers: shared hit=2543674 dirtied=15
8. 218.260 3,077.225 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.14..5,502,551.42 rows=216,604 width=764) (actual time=0.165..3,077.225 rows=200,302 loops=1)

  • Join Filter: (c.id_carrosserie = a.id_carrosserie)
  • Rows Removed by Join Filter: 798596
  • Buffers: shared hit=2543673 dirtied=15
9. 141.041 2,858.965 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=2.14..5,443,314.55 rows=216,604 width=756) (actual time=0.156..2,858.965 rows=200,302 loops=1)

  • Buffers: shared hit=2543672 dirtied=15
10. 218.349 2,317.320 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=1.85..5,377,244.05 rows=216,604 width=752) (actual time=0.143..2,317.320 rows=200,302 loops=1)

  • Buffers: shared hit=2002484 dirtied=15
11. 105.341 1,698.367 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=1.56..5,311,174.01 rows=216,604 width=751) (actual time=0.129..1,698.367 rows=200,302 loops=1)

  • Buffers: shared hit=1423905 dirtied=15
12. 137.607 1,192.422 ↑ 1.1 200,302 1

Nested Loop Left Join (cost=1.28..5,246,733.45 rows=216,604 width=748) (actual time=0.125..1,192.422 rows=200,302 loops=1)

  • Buffers: shared hit=852155 dirtied=15
13. 234.137 654.211 ↑ 1.1 200,302 1

Nested Loop (cost=1.00..5,182,294.24 rows=216,604 width=739) (actual time=0.111..654.211 rows=200,302 loops=1)

  • Buffers: shared hit=251224 dirtied=15
14. 9.457 17.899 ↓ 1.0 16,087 1

Nested Loop Left Join (cost=0.57..10,750.91 rows=15,793 width=50) (actual time=0.079..17.899 rows=16,087 loops=1)

  • Join Filter: (g.id_pays = pays.id_pays)
  • Buffers: shared hit=3085
15. 8.442 8.442 ↓ 1.0 16,087 1

Index Scan using geographie_id_pays_id_geographie_idx on geographie g (cost=0.42..10,509.91 rows=15,793 width=28) (actual time=0.042..8.442 rows=16,087 loops=1)

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=3082
16. 0.000 0.000 ↑ 1.0 1 16,087

Materialize (cost=0.14..4.11 rows=1 width=25) (actual time=0.000..0.000 rows=1 loops=16,087)

  • Buffers: shared hit=3
17. 0.003 0.034 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.14..4.10 rows=1 width=25) (actual time=0.032..0.034 rows=1 loops=1)

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

Seq Scan on pays (cost=0.00..1.71 rows=1 width=10) (actual time=0.018..0.020 rows=1 loops=1)

  • Filter: (id_pays = 'SE'::bpchar)
  • Rows Removed by Filter: 56
  • Buffers: shared hit=1
19. 0.011 0.011 ↑ 1.0 1 1

Index Scan using devise_pkey on devise (cost=0.14..2.36 rows=1 width=15) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (pays.id_devise = id_devise)
  • Buffers: shared hit=2
20. 402.175 402.175 ↑ 58.0 12 16,087

Index Scan using refresh_vo_all on annonce a (cost=0.43..320.50 rows=696 width=697) (actual time=0.002..0.025 rows=12 loops=16,087)

  • Index Cond: (id_geographie = g.id_geographie)
  • Buffers: shared hit=248139 dirtied=15
21. 400.604 400.604 ↑ 1.0 1 200,302

Index Scan using modele_pkey on modele m (cost=0.28..0.30 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=200,302)

  • Index Cond: (id_modele = a.id_modele)
  • Buffers: shared hit=600931
22. 400.604 400.604 ↑ 1.0 1 200,302

Index Scan using smodele_pkey on smodele sm (cost=0.28..0.30 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=200,302)

  • Index Cond: (a.id_smodele = id_smodele)
  • Buffers: shared hit=571750
23. 400.604 400.604 ↑ 1.0 1 200,302

Index Scan using generation_pkey on generation ge (cost=0.29..0.31 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=200,302)

  • Index Cond: (a.id_generation = id_generation)
  • Buffers: shared hit=578579
24. 400.604 400.604 ↑ 1.0 1 200,302

Index Scan using phase_pkey on phase p (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=200,302)

  • Index Cond: (a.id_phase = id_phase)
  • Buffers: shared hit=541188
25. 0.000 0.000 ↑ 3.8 5 200,302

Materialize (cost=0.00..1.28 rows=19 width=12) (actual time=0.000..0.000 rows=5 loops=200,302)

  • Buffers: shared hit=1
26. 0.012 0.012 ↑ 1.0 19 1

Seq Scan on carrosserie c (cost=0.00..1.19 rows=19 width=12) (actual time=0.005..0.012 rows=19 loops=1)

  • Buffers: shared hit=1
27. 0.000 0.000 ↑ 3.0 2 200,302

Materialize (cost=0.00..1.09 rows=6 width=11) (actual time=0.000..0.000 rows=2 loops=200,302)

  • Buffers: shared hit=1
28. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on energie e (cost=0.00..1.06 rows=6 width=11) (actual time=0.004..0.005 rows=6 loops=1)

  • Buffers: shared hit=1
29. 0.000 0.000 ↑ 1.0 2 200,302

Materialize (cost=0.00..1.03 rows=2 width=14) (actual time=0.000..0.000 rows=2 loops=200,302)

  • Buffers: shared hit=1
30. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on boite b (cost=0.00..1.02 rows=2 width=14) (actual time=0.005..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
31. 400.604 400.604 ↓ 0.0 0 200,302

Index Scan using vehicule_pkey on vehicule ve (cost=0.42..0.44 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=200,302)

  • Index Cond: (id_veh = a.id_veh)
  • Buffers: shared hit=306937
32. 600.906 600.906 ↑ 1.0 1 200,302

Index Scan using client_pkey on vendeur v (cost=0.43..0.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=200,302)

  • Index Cond: (a.id_vendeur = id_vendeur)
  • Buffers: shared hit=779299
33. 200.302 200.302 ↓ 0.0 0 200,302

Index Scan using dealer_pkey on dealer r (cost=0.28..0.30 rows=1 width=60) (actual time=0.001..0.001 rows=0 loops=200,302)

  • Index Cond: ((v.id_dealer_pro)::text = (id_dealer)::text)
  • Buffers: shared hit=46677
34. 0.000 0.000 ↓ 0.0 0 200,302

Index Scan using pk_network on network n (cost=0.14..0.16 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=200,302)

  • Index Cond: ((r.id_network)::text = (id_network)::text)
  • Buffers: shared hit=31118
35. 200.293 200.302 ↑ 1.2 13 200,302

Materialize (cost=0.00..1.22 rows=15 width=32) (actual time=0.000..0.001 rows=13 loops=200,302)

  • Buffers: shared hit=1
36. 0.009 0.009 ↑ 1.0 15 1

Seq Scan on outlier ou (cost=0.00..1.15 rows=15 width=32) (actual time=0.005..0.009 rows=15 loops=1)

  • Buffers: shared hit=1
37.          

SubPlan (forNested Loop Left Join)

38. 0.029 0.058 ↑ 100.0 1 29

Result (cost=0.00..2.52 rows=100 width=8) (actual time=0.001..0.002 rows=1 loops=29)

39. 0.029 0.029 ↑ 100.0 1 29

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.001..0.001 rows=1 loops=29)

40. 0.000 0.000 ↑ 1.0 1 29

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=29)

Planning time : 676.725 ms
Execution time : 6,055.633 ms