explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z7W4 : Optimization for: plan #CgMh

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 32,396.002 46,668.502 ↓ 46.2 5,218,810 1

Sort (cost=718,627.65..718,909.99 rows=112,936 width=513) (actual time=44,634.769..46,668.502 rows=5,218,810 loops=1)

  • Sort Key: i0_.oid
  • Sort Method: external merge Disk: 1419912kB
2. 1,707.614 14,272.500 ↓ 46.2 5,218,810 1

Hash Join (cost=608,029.86..656,649.41 rows=112,936 width=513) (actual time=10,486.757..14,272.500 rows=5,218,810 loops=1)

  • Hash Cond: (i1_.id_jdd = i2_.id_jdd)
3. 2,077.475 12,553.938 ↓ 17.1 400,645 1

Hash Join (cost=607,317.69..654,573.42 rows=23,442 width=513) (actual time=10,475.793..12,553.938 rows=400,645 loops=1)

  • Hash Cond: (g.cd_ref = i0_.cd_nom)
4. 1.067 1.307 ↑ 1.1 2,563 1

Bitmap Heap Scan on vm_requeteur_groupes g (cost=54.51..2,622.39 rows=2,850 width=4) (actual time=0.258..1.307 rows=2,563 loops=1)

  • Recheck Cond: (gid = 12)
5. 0.240 0.240 ↑ 1.1 2,563 1

Bitmap Index Scan on vm_requeteur_groupes_gid_idx (cost=0.00..53.80 rows=2,850 width=0) (actual time=0.240..0.240 rows=2,563 loops=1)

  • Index Cond: (gid = 12)
6. 553.521 10,475.156 ↓ 1.1 436,721 1

Hash (cost=575,259.35..575,259.35 rows=405,586 width=517) (actual time=10,475.156..10,475.156 rows=436,721 loops=1)

  • Buckets: 1024 Batches: 8192 (originally 256) Memory Usage: 3483kB
7. 4,030.573 9,921.635 ↓ 1.1 436,721 1

Hash Join (cost=204,525.88..575,259.35 rows=405,586 width=517) (actual time=2,754.097..9,921.635 rows=436,721 loops=1)

  • Hash Cond: (i0_.id_releve = i1_.rid)
8. 3,153.218 3,153.218 ↑ 1.0 4,124,641 1

Seq Scan on vm_requeteur_observations i0_ (cost=0.00..187,835.41 rows=4,124,641 width=105) (actual time=0.009..3,153.218 rows=4,124,641 loops=1)

9. 413.528 2,737.844 ↑ 1.0 395,935 1

Hash (cost=178,309.69..178,309.69 rows=395,935 width=412) (actual time=2,737.844..2,737.844 rows=395,935 loops=1)

  • Buckets: 1024 Batches: 256 Memory Usage: 595kB
10. 2,324.316 2,324.316 ↑ 1.0 395,935 1

Seq Scan on vm_requeteur_releves i1_ (cost=0.00..178,309.69 rows=395,935 width=412) (actual time=0.015..2,324.316 rows=395,935 loops=1)

  • Filter: ((departement)::text = '33'::text)
  • Rows Removed by Filter: 1688760
11. 2.665 10.948 ↓ 1.6 10,386 1

Hash (cost=630.51..630.51 rows=6,533 width=4) (actual time=10.948..10.948 rows=10,386 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 366kB
12. 3.123 8.283 ↓ 1.6 10,386 1

Hash Join (cost=400.50..630.51 rows=6,533 width=4) (actual time=2.822..8.283 rows=10,386 loops=1)

  • Hash Cond: (i2_.id_ca = i3_.id_ca)
13. 1.500 3.849 ↑ 1.0 3,279 1

Hash Right Join (cost=257.51..405.55 rows=3,328 width=8) (actual time=1.505..3.849 rows=3,279 loops=1)

  • Hash Cond: (i5_.id_jdd = i2_.id_jdd)
14. 0.860 0.860 ↑ 1.0 3,279 1

Seq Scan on jdd_contact_donnees i5_ (cost=0.00..102.28 rows=3,328 width=4) (actual time=0.003..0.860 rows=3,279 loops=1)

15. 0.391 1.489 ↑ 1.1 1,226 1

Hash (cost=240.56..240.56 rows=1,356 width=8) (actual time=1.489..1.489 rows=1,226 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
16. 1.098 1.098 ↑ 1.1 1,226 1

Seq Scan on jdd i2_ (cost=0.00..240.56 rows=1,356 width=8) (actual time=0.005..1.098 rows=1,226 loops=1)

17. 0.269 1.311 ↓ 1.2 1,044 1

Hash (cost=131.73..131.73 rows=901 width=4) (actual time=1.311..1.311 rows=1,044 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
18. 0.447 1.042 ↓ 1.2 1,044 1

Hash Right Join (cost=83.33..131.73 rows=901 width=4) (actual time=0.343..1.042 rows=1,044 loops=1)

  • Hash Cond: (i4_.id_ca = i3_.id_ca)
19. 0.270 0.270 ↓ 1.1 964 1

Seq Scan on ca_contact i4_ (cost=0.00..36.01 rows=901 width=4) (actual time=0.007..0.270 rows=964 loops=1)

20. 0.108 0.325 ↑ 1.1 409 1

Hash (cost=77.59..77.59 rows=459 width=4) (actual time=0.325..0.325 rows=409 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
21. 0.217 0.217 ↑ 1.1 409 1

Seq Scan on ca i3_ (cost=0.00..77.59 rows=459 width=4) (actual time=0.006..0.217 rows=409 loops=1)

Total runtime : 47,601.437 ms