explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 741.652 25,725.782 ↑ 1.1 200,176 1

Hash Left Join (cost=25,798.26..9,678,457.49 rows=216,594 width=925) (actual time=19,341.006..25,725.782 rows=200,176 loops=1)

  • Hash Cond: ((v.id_dealer_pro)::text = (r.id_dealer)::text)
  • Buffers: shared hit=14868910 read=7849 dirtied=3913
2. 197.854 24,979.712 ↑ 1.1 200,176 1

Nested Loop Left Join (cost=24,362.73..6,164,519.21 rows=216,594 width=875) (actual time=19,336.610..24,979.712 rows=200,176 loops=1)

  • Buffers: shared hit=14868483 read=7849 dirtied=3913
3. 80.539 24,381.506 ↑ 1.1 200,176 1

Hash Left Join (cost=24,362.31..6,047,835.92 rows=216,594 width=867) (actual time=19,336.594..24,381.506 rows=200,176 loops=1)

  • Hash Cond: (a.id_outlier = ou.id_outlier)
  • Buffers: shared hit=14089685 read=7849 dirtied=3913
4. 106.003 24,300.956 ↑ 1.1 200,176 1

Hash Left Join (cost=24,358.27..6,046,895.02 rows=216,594 width=839) (actual time=19,336.577..24,300.956 rows=200,176 loops=1)

  • Hash Cond: (pays.id_devise = devise.id_devise)
  • Buffers: shared hit=14089684 read=7849 dirtied=3913
5. 102.643 24,194.901 ↑ 1.1 200,176 1

Hash Left Join (cost=24,323.06..6,046,157.67 rows=216,594 width=824) (actual time=19,336.515..24,194.901 rows=200,176 loops=1)

  • Hash Cond: (g.id_pays = pays.id_pays)
  • Buffers: shared hit=14089682 read=7849 dirtied=3913
6. 99.529 24,092.244 ↑ 1.1 200,176 1

Hash Left Join (cost=24,320.51..6,023,683.49 rows=216,594 width=817) (actual time=19,336.493..24,092.244 rows=200,176 loops=1)

  • Hash Cond: (a.id_modele = m.id_modele)
  • Buffers: shared hit=14089680 read=7849 dirtied=3913
7. 81.636 23,991.919 ↑ 1.1 200,176 1

Hash Left Join (cost=23,708.02..6,022,494.96 rows=216,594 width=808) (actual time=19,335.688..23,991.919 rows=200,176 loops=1)

  • Hash Cond: (a.id_boite = b.id_boite)
  • Buffers: shared hit=14089658 read=7849 dirtied=3913
8. 113.989 23,910.277 ↑ 1.1 200,176 1

Nested Loop Left Join (cost=23,706.61..6,011,138.49 rows=216,594 width=798) (actual time=19,335.677..23,910.277 rows=200,176 loops=1)

  • Buffers: shared hit=14089657 read=7849 dirtied=3913
9. 95.275 23,395.936 ↑ 1.1 200,176 1

Hash Left Join (cost=23,706.19..5,895,277.32 rows=216,594 width=740) (actual time=19,335.669..23,395.936 rows=200,176 loops=1)

  • Hash Cond: (a.id_smodele = sm.id_smodele)
  • Buffers: shared hit=13782942 read=7837 dirtied=3913
10. 109.702 23,299.455 ↑ 1.1 200,176 1

Hash Left Join (cost=22,827.15..5,893,825.28 rows=216,594 width=737) (actual time=19,334.453..23,299.455 rows=200,176 loops=1)

  • Hash Cond: (a.id_generation = ge.id_generation)
  • Buffers: shared hit=13782892 read=7837 dirtied=3913
11. 79.176 23,183.356 ↑ 1.1 200,176 1

Hash Left Join (cost=18,428.60..5,888,857.25 rows=216,594 width=736) (actual time=19,328.011..23,183.356 rows=200,176 loops=1)

  • Hash Cond: (a.id_carrosserie = c.id_carrosserie)
  • Buffers: shared hit=13782718 read=7837 dirtied=3913
12. 103.989 23,104.170 ↑ 1.1 200,176 1

Hash Left Join (cost=18,423.75..5,887,299.60 rows=216,594 width=728) (actual time=19,327.994..23,104.170 rows=200,176 loops=1)

  • Hash Cond: (a.id_phase = p.id_phase)
  • Buffers: shared hit=13782717 read=7837 dirtied=3913
13. 80.294 22,993.981 ↑ 1.1 200,176 1

Hash Left Join (cost=13,117.03..5,881,423.61 rows=216,594 width=724) (actual time=19,321.748..22,993.981 rows=200,176 loops=1)

  • Hash Cond: (a.id_energie = e.id_energie)
  • Buffers: shared hit=13782539 read=7837 dirtied=3913
14. 3,614.986 22,913.677 ↑ 1.1 200,176 1

Hash Join (cost=13,114.82..5,877,221.84 rows=216,594 width=717) (actual time=19,321.725..22,913.677 rows=200,176 loops=1)

  • Hash Cond: (a.id_geographie = g.id_geographie)
  • Buffers: shared hit=13782538 read=7837 dirtied=3913
15. 19,290.996 19,290.996 ↓ 1.5 13,952,325 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.017..19,290.996 rows=13,952,325 loops=1)

  • Buffers: shared hit=13782065 read=7837 dirtied=3913
16. 2.725 7.695 ↓ 1.0 16,087 1

Hash (cost=11,495.50..11,495.50 rows=15,794 width=28) (actual time=7.695..7.695 rows=16,087 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1092kB
  • Buffers: shared hit=473
17. 4.111 4.970 ↓ 1.0 16,087 1

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

  • Recheck Cond: (id_pays = 'SE'::bpchar)
  • Heap Blocks: exact=426
  • Buffers: shared hit=473
18. 0.859 0.859 ↓ 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=0.859..0.859 rows=16,087 loops=1)

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=47
19. 0.004 0.010 ↑ 1.0 6 1

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

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

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

  • Buffers: shared hit=1
21. 3.115 6.200 ↑ 1.0 25,319 1

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

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

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

  • Buffers: shared hit=178
23. 0.004 0.010 ↑ 1.0 19 1

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

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

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

  • Buffers: shared hit=1
25. 2.848 6.397 ↑ 1.0 20,862 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1153kB
  • Buffers: shared hit=174
26. 3.549 3.549 ↑ 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.549 rows=20,862 loops=1)

  • Buffers: shared hit=174
27. 0.573 1.206 ↓ 1.0 4,098 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 245kB
  • Buffers: shared hit=50
28. 0.633 0.633 ↓ 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.633 rows=4,098 loops=1)

  • Buffers: shared hit=50
29. 400.352 400.352 ↓ 0.0 0 200,176

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

  • Index Cond: (id_veh = a.id_veh)
  • Buffers: shared hit=306715 read=12
30. 0.001 0.006 ↑ 1.0 2 1

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

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

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

  • Buffers: shared hit=1
32. 0.414 0.796 ↓ 1.0 2,919 1

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

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

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

  • Buffers: shared hit=22
34. 0.002 0.014 ↑ 1.0 1 1

Hash (cost=2.45..2.45 rows=1 width=10) (actual time=0.014..0.014 rows=1 loops=1)

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

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

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=2
36. 0.025 0.052 ↑ 1.0 164 1

Hash (cost=18.40..18.40 rows=164 width=15) (actual time=0.052..0.052 rows=164 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=2
37. 0.027 0.027 ↑ 1.0 164 1

Seq Scan on devise (cost=0.00..18.40 rows=164 width=15) (actual time=0.008..0.027 rows=164 loops=1)

  • Buffers: shared hit=2
38. 0.003 0.011 ↑ 1.0 15 1

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

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

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

  • Buffers: shared hit=1
40. 400.352 400.352 ↑ 1.0 1 200,176

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

  • Index Cond: (a.id_vendeur = id_vendeur)
  • Buffers: shared hit=778798
41. 0.931 4.360 ↑ 1.0 4,817 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 424kB
  • Buffers: shared hit=427
42. 1.083 3.429 ↑ 1.0 4,817 1

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

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

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

  • Buffers: shared hit=426
44. 0.008 0.019 ↑ 1.0 39 1

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

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

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

  • Buffers: shared hit=1
46.          

SubPlan (forHash Left Join)

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

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

49. 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 : 79.067 ms
Execution time : 25,742.980 ms