explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtII : Optimization for: plan #iqEt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 580.509 3,164.962 ↑ 1.1 200,334 1

Hash Left Join (cost=98,243.27..894,328.02 rows=218,110 width=927) (actual time=1,215.600..3,164.962 rows=200,334 loops=1)

  • Hash Cond: (a.id_outlier = ou.id_outlier)
  • Buffers: shared hit=298146 dirtied=64
2. 105.548 2,584.382 ↑ 1.1 200,334 1

Hash Left Join (cost=98,241.93..331,534.68 rows=218,110 width=909) (actual time=1,215.546..2,584.382 rows=200,334 loops=1)

  • Hash Cond: (a.id_veh = ve.id_veh)
  • Buffers: shared hit=298145 dirtied=64
3. 71.215 1,999.799 ↑ 1.1 200,334 1

Hash Left Join (cost=63,686.42..296,406.63 rows=218,110 width=851) (actual time=731.620..1,999.799 rows=200,334 loops=1)

  • Hash Cond: (a.id_boite = b.id_boite)
  • Buffers: shared hit=279622 dirtied=64
4. 69.176 1,928.573 ↑ 1.1 200,334 1

Hash Left Join (cost=63,685.38..294,638.09 rows=218,110 width=841) (actual time=731.595..1,928.573 rows=200,334 loops=1)

  • Hash Cond: (a.id_energie = e.id_energie)
  • Buffers: shared hit=279621 dirtied=64
5. 72.169 1,859.380 ↑ 1.1 200,334 1

Hash Left Join (cost=63,684.24..293,662.15 rows=218,110 width=834) (actual time=731.568..1,859.380 rows=200,334 loops=1)

  • Hash Cond: (a.id_carrosserie = c.id_carrosserie)
  • Buffers: shared hit=279620 dirtied=64
6. 90.432 1,787.190 ↑ 1.1 200,334 1

Hash Left Join (cost=63,682.82..292,978.64 rows=218,110 width=826) (actual time=731.530..1,787.190 rows=200,334 loops=1)

  • Hash Cond: (a.id_phase = p.id_phase)
  • Buffers: shared hit=279619 dirtied=64
7. 92.112 1,683.552 ↑ 1.1 200,334 1

Hash Left Join (cost=62,934.96..291,658.17 rows=218,110 width=822) (actual time=718.172..1,683.552 rows=200,334 loops=1)

  • Hash Cond: (a.id_generation = ge.id_generation)
  • Buffers: shared hit=279441 dirtied=64
8. 80.898 1,579.435 ↑ 1.1 200,334 1

Hash Left Join (cost=62,291.56..290,442.13 rows=218,110 width=821) (actual time=706.114..1,579.435 rows=200,334 loops=1)

  • Hash Cond: (a.id_smodele = sm.id_smodele)
  • Buffers: shared hit=279267 dirtied=64
9. 79.739 1,497.362 ↑ 1.1 200,334 1

Hash Left Join (cost=62,149.45..289,726.98 rows=218,110 width=818) (actual time=704.929..1,497.362 rows=200,334 loops=1)

  • Hash Cond: (a.id_modele = m.id_modele)
  • Buffers: shared hit=279217 dirtied=64
10. 59.267 1,416.817 ↑ 1.1 200,334 1

Hash Left Join (cost=62,061.84..289,066.00 rows=218,110 width=809) (actual time=704.110..1,416.817 rows=200,334 loops=1)

  • Hash Cond: ((v.id_dealer_pro)::text = (r.id_dealer)::text)
  • Buffers: shared hit=279195 dirtied=64
11. 81.272 1,352.996 ↑ 1.1 200,334 1

Hash Left Join (cost=61,511.56..287,681.66 rows=218,110 width=750) (actual time=699.534..1,352.996 rows=200,334 loops=1)

  • Hash Cond: (a.id_vendeur = v.id_vendeur)
  • Buffers: shared hit=278768 dirtied=64
12. 205.050 575.284 ↑ 1.1 200,334 1

Nested Loop (cost=1.00..225,598.57 rows=218,110 width=742) (actual time=0.106..575.284 rows=200,334 loops=1)

  • Buffers: shared hit=251574 dirtied=64
13. 8.768 16.320 ↓ 1.0 16,087 1

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

  • Join Filter: (g.id_pays = pays.id_pays)
  • Buffers: shared hit=3085
14. 7.552 7.552 ↓ 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.040..7.552 rows=16,087 loops=1)

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=3082
15. 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
16. 0.003 0.030 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: (pays.id_devise = id_devise)
  • Buffers: shared hit=2
19. 353.914 353.914 ↓ 1.1 12 16,087

Index Scan using refresh_vo_all on annonce a (cost=0.43..13.49 rows=11 width=700) (actual time=0.001..0.022 rows=12 loops=16,087)

  • Index Cond: (id_geographie = g.id_geographie)
  • Buffers: shared hit=248489 dirtied=64
20. 303.024 696.440 ↓ 1.0 1,525,432 1

Hash (cost=42,445.80..42,445.80 rows=1,525,180 width=16) (actual time=696.440..696.440 rows=1,525,432 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 70175kB
  • Buffers: shared hit=27194
21. 393.416 393.416 ↓ 1.0 1,525,432 1

Seq Scan on vendeur v (cost=0.00..42,445.80 rows=1,525,180 width=16) (actual time=0.009..393.416 rows=1,525,432 loops=1)

  • Buffers: shared hit=27194
22. 0.929 4.554 ↑ 1.0 4,817 1

Hash (cost=490.07..490.07 rows=4,817 width=71) (actual time=4.554..4.554 rows=4,817 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 424kB
  • Buffers: shared hit=427
23. 1.051 3.625 ↑ 1.0 4,817 1

Hash Left Join (cost=1.88..490.07 rows=4,817 width=71) (actual time=0.045..3.625 rows=4,817 loops=1)

  • Hash Cond: ((r.id_network)::text = (n.id_network)::text)
  • Buffers: shared hit=427
24. 2.544 2.544 ↑ 1.0 4,817 1

Seq Scan on dealer r (cost=0.00..474.17 rows=4,817 width=60) (actual time=0.008..2.544 rows=4,817 loops=1)

  • Buffers: shared hit=426
25. 0.010 0.030 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=19) (actual time=0.030..0.030 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
26. 0.020 0.020 ↑ 1.0 39 1

Seq Scan on network n (cost=0.00..1.39 rows=39 width=19) (actual time=0.004..0.020 rows=39 loops=1)

  • Buffers: shared hit=1
27. 0.417 0.806 ↓ 1.0 2,919 1

Hash (cost=51.16..51.16 rows=2,916 width=17) (actual time=0.806..0.806 rows=2,919 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
  • Buffers: shared hit=22
28. 0.389 0.389 ↓ 1.0 2,919 1

Seq Scan on modele m (cost=0.00..51.16 rows=2,916 width=17) (actual time=0.006..0.389 rows=2,919 loops=1)

  • Buffers: shared hit=22
29. 0.562 1.175 ↓ 1.0 4,098 1

Hash (cost=90.94..90.94 rows=4,094 width=11) (actual time=1.175..1.175 rows=4,098 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 245kB
  • Buffers: shared hit=50
30. 0.613 0.613 ↓ 1.0 4,098 1

Seq Scan on smodele sm (cost=0.00..90.94 rows=4,094 width=11) (actual time=0.005..0.613 rows=4,098 loops=1)

  • Buffers: shared hit=50
31. 5.375 12.005 ↑ 1.0 20,862 1

Hash (cost=382.62..382.62 rows=20,862 width=9) (actual time=12.005..12.005 rows=20,862 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1153kB
  • Buffers: shared hit=174
32. 6.630 6.630 ↑ 1.0 20,862 1

Seq Scan on generation ge (cost=0.00..382.62 rows=20,862 width=9) (actual time=0.005..6.630 rows=20,862 loops=1)

  • Buffers: shared hit=174
33. 6.536 13.206 ↑ 1.0 25,319 1

Hash (cost=431.27..431.27 rows=25,327 width=8) (actual time=13.206..13.206 rows=25,319 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1246kB
  • Buffers: shared hit=178
34. 6.670 6.670 ↑ 1.0 25,319 1

Seq Scan on phase p (cost=0.00..431.27 rows=25,327 width=8) (actual time=0.011..6.670 rows=25,319 loops=1)

  • Buffers: shared hit=178
35. 0.010 0.021 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=12) (actual time=0.021..0.021 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
36. 0.011 0.011 ↑ 1.0 19 1

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

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

Hash (cost=1.06..1.06 rows=6 width=11) (actual time=0.017..0.017 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
38. 0.012 0.012 ↑ 1.0 6 1

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

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

Hash (cost=1.02..1.02 rows=2 width=14) (actual time=0.011..0.011 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
40. 0.006 0.006 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
41. 236.367 479.035 ↑ 1.0 712,556 1

Hash (cost=25,648.56..25,648.56 rows=712,556 width=62) (actual time=479.035..479.035 rows=712,556 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 71049kB
  • Buffers: shared hit=18523
42. 242.668 242.668 ↑ 1.0 712,556 1

Seq Scan on vehicule ve (cost=0.00..25,648.56 rows=712,556 width=62) (actual time=0.014..242.668 rows=712,556 loops=1)

  • Buffers: shared hit=18523
43. 0.005 0.013 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=32) (actual time=0.013..0.013 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
44. 0.008 0.008 ↑ 1.0 15 1

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

  • Buffers: shared hit=1
45.          

SubPlan (forHash Left Join)

46. 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)

47. 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)

48. 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 : 947.373 ms
Execution time : 3,178.270 ms