explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 679.907 5,841.280 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=3.41..6,440,890.05 rows=216,594 width=925) (actual time=328.694..5,841.280 rows=200,269 loops=1)

  • Buffers: shared hit=4277898 read=799 dirtied=638
2. 73.538 4,760.777 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=2.99..5,786,251.30 rows=216,594 width=876) (actual time=328.664..4,760.777 rows=200,269 loops=1)

  • Buffers: shared hit=3971077 read=799 dirtied=638
3. 49.552 4,286.701 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=2.70..5,720,183.85 rows=216,594 width=872) (actual time=328.649..4,286.701 rows=200,269 loops=1)

  • Buffers: shared hit=3429964 read=799 dirtied=638
4. 150.321 3,836.611 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=2.42..5,655,747.60 rows=216,594 width=863) (actual time=328.635..3,836.611 rows=200,269 loops=1)

  • Buffers: shared hit=2829132 read=799 dirtied=638
5. 124.941 3,285.752 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=2.13..5,589,680.62 rows=216,594 width=862) (actual time=328.621..3,285.752 rows=200,269 loops=1)

  • Join Filter: (e.id_energie = a.id_energie)
  • Rows Removed by Join Filter: 223512
  • Buffers: shared hit=2250637 read=799 dirtied=638
6. 159.022 3,160.811 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=2.13..5,572,494.33 rows=216,594 width=855) (actual time=328.613..3,160.811 rows=200,269 loops=1)

  • Buffers: shared hit=2250636 read=799 dirtied=638
7. 205.969 3,001.789 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.99..5,538,325.56 rows=216,594 width=844) (actual time=328.607..3,001.789 rows=200,269 loops=1)

  • Buffers: shared hit=2219516 read=799 dirtied=638
8. 177.034 2,795.820 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.71..5,473,274.72 rows=216,594 width=796) (actual time=328.600..2,795.820 rows=200,269 loops=1)

  • Join Filter: (ou.id_outlier = a.id_outlier)
  • Rows Removed by Join Filter: 2539065
  • Buffers: shared hit=2172836 read=799 dirtied=638
9. 116.151 2,418.517 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.71..5,425,256.90 rows=216,594 width=768) (actual time=328.583..2,418.517 rows=200,269 loops=1)

  • Join Filter: (b.id_boite = a.id_boite)
  • Rows Removed by Join Filter: 121363
  • Buffers: shared hit=2172835 read=799 dirtied=638
10. 49.410 2,302.366 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.71..5,419,824.71 rows=216,594 width=758) (actual time=328.574..2,302.366 rows=200,269 loops=1)

  • Buffers: shared hit=2172834 read=799 dirtied=638
11. 222.562 1,852.418 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.43..5,355,387.12 rows=216,594 width=755) (actual time=328.567..1,852.418 rows=200,269 loops=1)

  • Buffers: shared hit=1601168 read=799 dirtied=638
12. 191.041 1,229.318 ↑ 1.1 200,269 1

Nested Loop Left Join (cost=1.00..5,258,197.29 rows=216,594 width=747) (actual time=328.548..1,229.318 rows=200,269 loops=1)

  • Join Filter: (c.id_carrosserie = a.id_carrosserie)
  • Rows Removed by Join Filter: 798250
  • Buffers: shared hit=822002 read=799 dirtied=638
13. 206.857 1,038.277 ↑ 1.1 200,269 1

Nested Loop (cost=1.00..5,198,963.27 rows=216,594 width=739) (actual time=328.533..1,038.277 rows=200,269 loops=1)

  • Buffers: shared hit=822001 read=799 dirtied=638
14. 7.896 397.071 ↓ 1.0 16,087 1

Nested Loop Left Join (cost=0.57..27,398.12 rows=15,794 width=50) (actual time=328.503..397.071 rows=16,087 loops=1)

  • Join Filter: (g.id_pays = pays.id_pays)
  • Buffers: shared hit=570880 read=799
15. 389.175 389.175 ↓ 1.0 16,087 1

Index Scan using geographie_pkey on geographie g (cost=0.42..27,157.10 rows=15,794 width=28) (actual time=328.464..389.175 rows=16,087 loops=1)

  • Filter: (id_pays = 'SE'::bpchar)
  • Rows Removed by Filter: 661705
  • Buffers: shared hit=570877 read=799
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.007 0.031 ↑ 1.0 1 1

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

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

Seq Scan on pays (cost=0.00..1.71 rows=1 width=10) (actual time=0.013..0.013 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. 434.349 434.349 ↑ 58.0 12 16,087

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

  • Index Cond: (id_geographie = g.id_geographie)
  • Buffers: shared hit=251121 dirtied=638
21. 0.000 0.000 ↑ 3.8 5 200,269

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

  • Buffers: shared hit=1
22. 0.008 0.008 ↑ 1.0 19 1

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

  • Buffers: shared hit=1
23. 400.538 400.538 ↑ 1.0 1 200,269

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

  • Index Cond: (a.id_vendeur = id_vendeur)
  • Buffers: shared hit=779166
24. 400.538 400.538 ↑ 1.0 1 200,269

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,269)

  • Index Cond: (a.id_smodele = id_smodele)
  • Buffers: shared hit=571666
25. 0.000 0.000 ↑ 1.0 2 200,269

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

  • Buffers: shared hit=1
26. 0.004 0.004 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
27. 200.263 200.269 ↑ 1.2 13 200,269

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

  • Buffers: shared hit=1
28. 0.006 0.006 ↑ 1.0 15 1

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

  • Buffers: shared hit=1
29. 0.000 0.000 ↓ 0.0 0 200,269

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

  • Index Cond: ((v.id_dealer_pro)::text = (id_dealer)::text)
  • Buffers: shared hit=46680
30. 0.000 0.000 ↓ 0.0 0 200,269

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,269)

  • Index Cond: ((r.id_network)::text = (id_network)::text)
  • Buffers: shared hit=31120
31. 0.000 0.000 ↑ 3.0 2 200,269

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

  • Buffers: shared hit=1
32. 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
33. 400.538 400.538 ↑ 1.0 1 200,269

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,269)

  • Index Cond: (a.id_generation = id_generation)
  • Buffers: shared hit=578495
34. 400.538 400.538 ↑ 1.0 1 200,269

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,269)

  • Index Cond: (id_modele = a.id_modele)
  • Buffers: shared hit=600832
35. 400.538 400.538 ↑ 1.0 1 200,269

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,269)

  • Index Cond: (a.id_phase = id_phase)
  • Buffers: shared hit=541113
36. 400.538 400.538 ↓ 0.0 0 200,269

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,269)

  • Index Cond: (id_veh = a.id_veh)
  • Buffers: shared hit=306821
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 : 95.507 ms
Execution time : 5,856.862 ms