explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iqEt

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 767.935 25,386.152 ↑ 1.1 199,950 1

Hash Left Join (cost=112,744.34..5,427,006.86 rows=216,594 width=925) (actual time=1,737.593..25,386.152 rows=199,950 loops=1)

  • Hash Cond: (a.id_smodele = sm.id_smodele)
  • Buffers: shared hit=4341944 read=73232
2. 85.769 24,616.719 ↑ 1.1 199,950 1

Hash Left Join (cost=112,602.23..4,868,024.66 rows=216,594 width=931) (actual time=1,736.258..24,616.719 rows=199,950 loops=1)

  • Hash Cond: ((r.id_network)::text = (n.id_network)::text)
  • Buffers: shared hit=4341894 read=73232
3. 95.990 24,530.931 ↑ 1.1 199,950 1

Hash Left Join (cost=112,600.35..4,867,392.44 rows=216,594 width=920) (actual time=1,736.223..24,530.931 rows=199,950 loops=1)

  • Hash Cond: (a.id_energie = e.id_energie)
  • Buffers: shared hit=4341893 read=73232
4. 88.586 24,434.932 ↑ 1.1 199,950 1

Hash Left Join (cost=112,599.21..4,866,423.29 rows=216,594 width=913) (actual time=1,736.200..24,434.932 rows=199,950 loops=1)

  • Hash Cond: (a.id_outlier = ou.id_outlier)
  • Buffers: shared hit=4341892 read=73232
5. 125.322 24,346.334 ↑ 1.1 199,950 1

Hash Left Join (cost=112,597.88..4,865,812.88 rows=216,594 width=885) (actual time=1,736.175..24,346.334 rows=199,950 loops=1)

  • Hash Cond: (a.id_modele = m.id_modele)
  • Buffers: shared hit=4341891 read=73232
6. 138.215 24,220.138 ↑ 1.1 199,950 1

Hash Left Join (cost=112,510.27..4,865,155.88 rows=216,594 width=876) (actual time=1,735.274..24,220.138 rows=199,950 loops=1)

  • Hash Cond: (a.id_veh = ve.id_veh)
  • Buffers: shared hit=4341869 read=73232
7. 145.400 23,439.798 ↑ 1.1 199,950 1

Hash Left Join (cost=77,954.76..4,830,031.82 rows=216,594 width=818) (actual time=1,089.144..23,439.798 rows=199,950 loops=1)

  • Hash Cond: (a.id_generation = ge.id_generation)
  • Buffers: shared hit=4324857 read=71721
8. 88.785 23,282.642 ↑ 1.1 199,950 1

Hash Left Join (cost=77,311.36..4,828,819.76 rows=216,594 width=817) (actual time=1,077.251..23,282.642 rows=199,950 loops=1)

  • Hash Cond: (a.id_boite = b.id_boite)
  • Buffers: shared hit=4324683 read=71721
9. 92.453 23,193.849 ↑ 1.1 199,950 1

Hash Left Join (cost=77,310.32..4,827,063.51 rows=216,594 width=807) (actual time=1,077.234..23,193.849 rows=199,950 loops=1)

  • Hash Cond: (a.id_carrosserie = c.id_carrosserie)
  • Buffers: shared hit=4324682 read=71721
10. 133.306 23,101.379 ↑ 1.1 199,950 1

Hash Left Join (cost=77,308.89..4,826,385.24 rows=216,594 width=799) (actual time=1,077.200..23,101.379 rows=199,950 loops=1)

  • Hash Cond: (a.id_phase = p.id_phase)
  • Buffers: shared hit=4324681 read=71721
11. 197.179 22,958.088 ↑ 1.1 199,950 1

Hash Left Join (cost=76,561.03..4,825,068.75 rows=216,594 width=795) (actual time=1,067.096..22,958.088 rows=199,950 loops=1)

  • Hash Cond: (a.id_vendeur = v.id_vendeur)
  • Buffers: shared hit=4324503 read=71721
12. 2,613.251 21,710.430 ↑ 1.1 199,950 1

Hash Join (cost=10,512.48..4,756,706.83 rows=216,594 width=739) (actual time=13.931..21,710.430 rows=199,950 loops=1)

  • Hash Cond: (a.id_geographie = g.id_geographie)
  • Buffers: shared hit=4296883 read=71721
13. 19,083.352 19,083.352 ↓ 1.5 13,949,691 1

Seq Scan on annonce a (cost=0.00..4,709,168.16 rows=9,296,066 width=697) (actual time=0.013..19,083.352 rows=13,949,691 loops=1)

  • Filter: ((id_modele IS NOT NULL) AND (id_doublon IS NOT NULL) AND (id_modele <> 0) AND (id_categorie = 3) AND ((id_outlier IS NULL) OR (id_outlier <> ALL ('{7,8,9,14}'::integer[]))))
  • Rows Removed by Filter: 3104765
  • Buffers: shared hit=4296407 read=71721
14. 3.354 13.827 ↓ 1.0 16,087 1

Hash (cost=10,315.05..10,315.05 rows=15,794 width=50) (actual time=13.827..13.827 rows=16,087 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1491kB
  • Buffers: shared hit=476
15. 6.917 10.473 ↓ 1.0 16,087 1

Nested Loop Left Join (cost=171.37..10,315.05 rows=15,794 width=50) (actual time=0.841..10.473 rows=16,087 loops=1)

  • Join Filter: (g.id_pays = pays.id_pays)
  • Buffers: shared hit=476
16. 2.779 3.556 ↓ 1.0 16,087 1

Bitmap Heap Scan on geographie g (cost=171.23..10,074.04 rows=15,794 width=28) (actual time=0.816..3.556 rows=16,087 loops=1)

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

  • Index Cond: (id_pays = 'SE'::bpchar)
  • Buffers: shared hit=47
18. 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
19. 0.004 0.022 ↑ 1.0 1 1

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

  • Buffers: shared hit=3
20. 0.011 0.011 ↑ 1.0 1 1

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

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

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

  • Index Cond: (pays.id_devise = id_devise)
  • Buffers: shared hit=2
22. 366.437 1,050.479 ↓ 1.0 1,525,253 1

Hash (cost=46,983.80..46,983.80 rows=1,525,180 width=64) (actual time=1,050.479..1,050.479 rows=1,525,253 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 70490kB
  • Buffers: shared hit=27620
23. 238.976 684.042 ↓ 1.0 1,525,253 1

Hash Left Join (cost=534.38..46,983.80 rows=1,525,180 width=64) (actual time=3.402..684.042 rows=1,525,253 loops=1)

  • Hash Cond: ((v.id_dealer_pro)::text = (r.id_dealer)::text)
  • Buffers: shared hit=27620
24. 441.694 441.694 ↓ 1.0 1,525,253 1

Seq Scan on vendeur v (cost=0.00..42,445.80 rows=1,525,180 width=16) (actual time=0.010..441.694 rows=1,525,253 loops=1)

  • Buffers: shared hit=27194
25. 0.891 3.372 ↑ 1.0 4,817 1

Hash (cost=474.17..474.17 rows=4,817 width=60) (actual time=3.372..3.372 rows=4,817 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 402kB
  • Buffers: shared hit=426
26. 2.481 2.481 ↑ 1.0 4,817 1

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

  • Buffers: shared hit=426
27. 4.881 9.985 ↑ 1.0 25,319 1

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

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

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

  • Buffers: shared hit=178
29. 0.007 0.017 ↑ 1.0 19 1

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

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

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

  • Buffers: shared hit=1
31. 0.003 0.008 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
32. 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.004..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
33. 5.086 11.756 ↑ 1.0 20,862 1

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

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

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

  • Buffers: shared hit=174
35. 283.616 642.125 ↑ 1.0 712,556 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 71049kB
  • Buffers: shared hit=17012 read=1511
36. 358.509 358.509 ↑ 1.0 712,556 1

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

  • Buffers: shared hit=17012 read=1511
37. 0.471 0.874 ↓ 1.0 2,919 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
  • Buffers: shared hit=22
38. 0.403 0.403 ↓ 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.403 rows=2,919 loops=1)

  • Buffers: shared hit=22
39. 0.005 0.012 ↑ 1.0 15 1

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

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

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

  • Buffers: shared hit=1
41. 0.004 0.009 ↑ 1.0 6 1

Hash (cost=1.06..1.06 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
42. 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.005..0.005 rows=6 loops=1)

  • Buffers: shared hit=1
43. 0.010 0.019 ↑ 1.0 39 1

Hash (cost=1.39..1.39 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
44. 0.009 0.009 ↑ 1.0 39 1

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

  • Buffers: shared hit=1
45. 0.650 1.295 ↓ 1.0 4,098 1

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

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

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

  • Buffers: shared hit=50
47.          

SubPlan (forHash Left Join)

48. 0.058 0.203 ↑ 100.0 1 29

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

49. 0.116 0.145 ↑ 100.0 1 29

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

50. 0.029 0.029 ↑ 1.0 1 29

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

Planning time : 81.685 ms
Execution time : 25,404.914 ms