explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SBbB : Optimization for: plan #iqEt

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 643.148 28,556.174 ↑ 1.1 200,079 1

Hash Left Join (cost=603,087.17..9,623,564.87 rows=216,594 width=925) (actual time=25,589.787..28,556.174 rows=200,079 loops=1)

  • Hash Cond: (a.id_outlier = ou.id_outlier)
  • Buffers: shared hit=13479666 read=63389 dirtied=33527
2. 75.759 27,912.953 ↑ 1.1 200,079 1

Hash Left Join (cost=603,083.13..6,111,093.75 rows=216,594 width=906) (actual time=25,589.732..27,912.953 rows=200,079 loops=1)

  • Hash Cond: ((v.id_dealer_pro)::text = (r.id_dealer)::text)
  • Buffers: shared hit=13479665 read=63389 dirtied=33527
3. 151.619 27,820.689 ↑ 1.1 200,079 1

Nested Loop Left Join (cost=601,647.61..6,108,685.70 rows=216,594 width=847) (actual time=25,573.203..27,820.689 rows=200,079 loops=1)

  • Buffers: shared hit=13479665 read=62962 dirtied=33527
4. 72.250 27,268.912 ↑ 1.1 200,079 1

Hash Left Join (cost=601,647.18..5,992,002.40 rows=216,594 width=839) (actual time=25,573.181..27,268.912 rows=200,079 loops=1)

  • Hash Cond: (a.id_carrosserie = c.id_carrosserie)
  • Buffers: shared hit=12701260 read=62953 dirtied=33527
5. 92.891 27,196.651 ↑ 1.1 200,079 1

Hash Left Join (cost=601,642.33..5,990,444.74 rows=216,594 width=831) (actual time=25,573.157..27,196.651 rows=200,079 loops=1)

  • Hash Cond: (a.id_modele = m.id_modele)
  • Buffers: shared hit=12701259 read=62953 dirtied=33527
6. 109.827 27,102.911 ↑ 1.1 200,079 1

Hash Left Join (cost=601,029.84..5,989,256.21 rows=216,594 width=822) (actual time=25,572.291..27,102.911 rows=200,079 loops=1)

  • Hash Cond: (a.id_phase = p.id_phase)
  • Buffers: shared hit=12701237 read=62953 dirtied=33527
7. 96.130 26,986.409 ↑ 1.1 200,079 1

Hash Left Join (cost=595,723.13..5,983,380.22 rows=216,594 width=818) (actual time=25,565.549..26,986.409 rows=200,079 loops=1)

  • Hash Cond: (a.id_smodele = sm.id_smodele)
  • Buffers: shared hit=12701059 read=62953 dirtied=33527
8. 105.192 26,889.007 ↑ 1.1 200,079 1

Hash Left Join (cost=594,844.09..5,981,928.18 rows=216,594 width=815) (actual time=25,564.262..26,889.007 rows=200,079 loops=1)

  • Hash Cond: (a.id_generation = ge.id_generation)
  • Buffers: shared hit=12701009 read=62953 dirtied=33527
9. 72.744 26,777.103 ↑ 1.1 200,079 1

Hash Left Join (cost=590,445.54..5,976,960.16 rows=216,594 width=814) (actual time=25,557.482..26,777.103 rows=200,079 loops=1)

  • Hash Cond: (a.id_energie = e.id_energie)
  • Buffers: shared hit=12700835 read=62953 dirtied=33527
10. 88.081 26,704.350 ↑ 1.1 200,079 1

Nested Loop Left Join (cost=590,443.32..5,972,758.39 rows=216,594 width=807) (actual time=25,557.464..26,704.350 rows=200,079 loops=1)

  • Buffers: shared hit=12700834 read=62953 dirtied=33527
11. 73.339 26,216.111 ↑ 1.1 200,079 1

Hash Left Join (cost=590,442.90..5,856,897.22 rows=216,594 width=749) (actual time=25,557.455..26,216.111 rows=200,079 loops=1)

  • Hash Cond: (a.id_boite = b.id_boite)
  • Buffers: shared hit=12394621 read=62555 dirtied=33527
12. 3,110.891 26,142.762 ↑ 1.1 200,079 1

Merge Join (cost=590,441.49..5,845,540.75 rows=216,594 width=739) (actual time=25,557.428..26,142.762 rows=200,079 loops=1)

  • Merge Cond: (g.id_geographie = a.id_geographie)
  • Buffers: shared hit=12394620 read=62555 dirtied=33527
13. 9.816 24.099 ↓ 1.0 16,087 1

Sort (cost=14,260.20..14,299.69 rows=15,794 width=50) (actual time=22.476..24.099 rows=16,087 loops=1)

  • Sort Key: g.id_geographie
  • Sort Method: quicksort Memory: 2391kB
  • Buffers: shared hit=477
14. 10.261 14.283 ↓ 1.0 16,087 1

Nested Loop Left Join (cost=171.51..13,158.80 rows=15,794 width=50) (actual time=1.393..14.283 rows=16,087 loops=1)

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

Bitmap Heap Scan on geographie g (cost=171.23..11,495.50 rows=15,794 width=28) (actual time=1.365..4.022 rows=16,087 loops=1)

  • Recheck Cond: (id_pays = 'SE'::bpchar)
  • Heap Blocks: exact=426
  • Buffers: shared hit=473
16. 1.317 1.317 ↓ 1.0 16,087 1

Bitmap Index Scan on geographie_id_pays_idx (cost=0.00..167.28 rows=15,794 width=0) (actual time=1.317..1.317 rows=16,087 loops=1)

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

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

  • Buffers: shared hit=4
18. 0.004 0.024 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..4.93 rows=1 width=25) (actual time=0.023..0.024 rows=1 loops=1)

  • Buffers: shared hit=4
19. 0.011 0.011 ↑ 1.0 1 1

Index Scan using id_pays_pkey on pays (cost=0.14..2.45 rows=1 width=10) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=2
20. 0.009 0.009 ↑ 1.0 1 1

Index Scan using devise_pkey on devise (cost=0.14..2.45 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
21. 23,007.772 23,007.772 ↓ 1.3 12,467,632 1

Index Scan using refresh_vo_all on annonce a (cost=0.43..5,839,703.87 rows=9,296,066 width=697) (actual time=0.032..23,007.772 rows=12,467,632 loops=1)

  • Buffers: shared hit=12394143 read=62555 dirtied=33527
22. 0.004 0.010 ↑ 1.0 2 1

Hash (cost=1.20..1.20 rows=2 width=14) (actual time=0.010..0.010 rows=2 loops=1)

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

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

  • Buffers: shared hit=1
24. 400.158 400.158 ↓ 0.0 0 200,079

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

  • Index Cond: (id_veh = a.id_veh)
  • Buffers: shared hit=306213 read=398
25. 0.004 0.009 ↑ 1.0 6 1

Hash (cost=1.60..1.60 rows=6 width=11) (actual time=0.009..0.009 rows=6 loops=1)

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

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

  • Buffers: shared hit=1
27. 3.042 6.712 ↑ 1.0 20,862 1

Hash (cost=2,260.20..2,260.20 rows=20,862 width=9) (actual time=6.712..6.712 rows=20,862 loops=1)

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

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

  • Buffers: shared hit=174
29. 0.604 1.272 ↓ 1.0 4,098 1

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

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

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

  • Buffers: shared hit=50
31. 3.373 6.675 ↑ 1.0 25,319 1

Hash (cost=2,710.70..2,710.70 rows=25,327 width=8) (actual time=6.675..6.675 rows=25,319 loops=1)

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

Seq Scan on phase p (cost=0.00..2,710.70 rows=25,327 width=8) (actual time=0.007..3.302 rows=25,319 loops=1)

  • Buffers: shared hit=178
33. 0.444 0.849 ↓ 1.0 2,919 1

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

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

Seq Scan on modele m (cost=0.00..313.60 rows=2,916 width=17) (actual time=0.007..0.405 rows=2,919 loops=1)

  • Buffers: shared hit=22
35. 0.004 0.011 ↑ 1.0 19 1

Hash (cost=2.90..2.90 rows=19 width=12) (actual time=0.011..0.011 rows=19 loops=1)

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

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

  • Buffers: shared hit=1
37. 400.158 400.158 ↑ 1.0 1 200,079

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

  • Index Cond: (a.id_vendeur = id_vendeur)
  • Buffers: shared hit=778405 read=9
38. 1.638 16.505 ↑ 1.0 4,817 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 424kB
  • Buffers: shared read=427
39. 1.643 14.867 ↑ 1.0 4,817 1

Hash Left Join (cost=8.90..941.78 rows=4,817 width=71) (actual time=0.268..14.867 rows=4,817 loops=1)

  • Hash Cond: ((r.id_network)::text = (n.id_network)::text)
  • Buffers: shared read=427
40. 12.991 12.991 ↑ 1.0 4,817 1

Seq Scan on dealer r (cost=0.00..907.70 rows=4,817 width=60) (actual time=0.024..12.991 rows=4,817 loops=1)

  • Buffers: shared read=426
41. 0.010 0.233 ↑ 1.0 39 1

Hash (cost=4.90..4.90 rows=39 width=19) (actual time=0.232..0.233 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared read=1
42. 0.223 0.223 ↑ 1.0 39 1

Seq Scan on network n (cost=0.00..4.90 rows=39 width=19) (actual time=0.217..0.223 rows=39 loops=1)

  • Buffers: shared read=1
43. 0.005 0.015 ↑ 1.0 15 1

Hash (cost=2.50..2.50 rows=15 width=32) (actual time=0.015..0.015 rows=15 loops=1)

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

Seq Scan on outlier ou (cost=0.00..2.50 rows=15 width=32) (actual time=0.006..0.010 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..16.15 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..5.15 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.10 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=29)

Planning time : 123.983 ms
Execution time : 28,573.086 ms