explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B1oI : Optimization for: plan #1D3k

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,556.339 13,490.760 ↓ 46.2 5,218,810 1

Hash Join (cost=20,003,405,619.19..20,003,454,238.74 rows=112,936 width=513) (actual time=9,950.514..13,490.760 rows=5,218,810 loops=1)

  • Hash Cond: (i1_.id_jdd = i2_.id_jdd)
2. 1,983.305 11,493.002 ↓ 17.1 400,645 1

Hash Join (cost=3,381,237.55..3,428,493.28 rows=23,442 width=513) (actual time=9,509.064..11,493.002 rows=400,645 loops=1)

  • Hash Cond: (g.cd_ref = i0_.cd_nom)
3. 1.052 1.572 ↑ 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.547..1.572 rows=2,563 loops=1)

  • Recheck Cond: (gid = 12)
4. 0.520 0.520 ↑ 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.520..0.520 rows=2,563 loops=1)

  • Index Cond: (gid = 12)
5. 1,070.269 9,508.125 ↓ 1.1 436,721 1

Hash (cost=3,349,179.21..3,349,179.21 rows=405,586 width=517) (actual time=9,508.125..9,508.125 rows=436,721 loops=1)

  • Buckets: 1024 Batches: 8192 (originally 256) Memory Usage: 3483kB
6. 753.328 8,437.856 ↓ 1.1 436,721 1

Nested Loop (cost=7,417.35..3,349,179.21 rows=405,586 width=517) (actual time=242.606..8,437.856 rows=436,721 loops=1)

7. 1,145.430 1,349.568 ↑ 1.0 395,935 1

Bitmap Heap Scan on vm_requeteur_releves i1_ (cost=7,416.92..164,617.11 rows=395,935 width=412) (actual time=208.400..1,349.568 rows=395,935 loops=1)

  • Recheck Cond: ((departement)::text = '33'::text)
  • Rows Removed by Index Recheck: 1140119
8. 204.138 204.138 ↑ 1.0 395,935 1

Bitmap Index Scan on vm_requeteur_releves_departement_idx (cost=0.00..7,317.94 rows=395,935 width=0) (actual time=204.138..204.138 rows=395,935 loops=1)

  • Index Cond: ((departement)::text = '33'::text)
9. 6,334.960 6,334.960 ↑ 1.0 1 395,935

Index Scan using vm_requeteur_observations_id_releve_idx on vm_requeteur_observations i0_ (cost=0.43..8.03 rows=1 width=105) (actual time=0.015..0.016 rows=1 loops=395,935)

  • Index Cond: (id_releve = i1_.rid)
10. 3.667 441.419 ↓ 1.6 10,386 1

Hash (cost=20,000,024,299.98..20,000,024,299.98 rows=6,533 width=4) (actual time=441.419..441.419 rows=10,386 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 366kB
11. 4.768 437.752 ↓ 1.6 10,386 1

Merge Left Join (cost=20,000,000,377.75..20,000,024,299.98 rows=6,533 width=4) (actual time=13.441..437.752 rows=10,386 loops=1)

  • Merge Cond: (i2_.id_jdd = i5_.id_jdd)
12. 235.813 429.165 ↓ 1.2 3,276 1

Nested Loop (cost=10,000,000,080.78..10,000,023,898.35 rows=2,662 width=4) (actual time=11.506..429.165 rows=3,276 loops=1)

  • Join Filter: (i3_.id_ca = i2_.id_ca)
  • Rows Removed by Join Filter: 1276668
13. 3.322 3.322 ↑ 1.1 1,226 1

Index Scan using pkey_jdd on jdd i2_ (cost=0.28..4,738.59 rows=1,356 width=8) (actual time=0.024..3.322 rows=1,226 loops=1)

14. 177.586 190.030 ↓ 1.2 1,044 1,226

Materialize (cost=10,000,000,080.50..10,000,000,835.67 rows=901 width=4) (actual time=0.010..0.155 rows=1,044 loops=1,226)

15. 0.478 12.444 ↓ 1.2 1,044 1

Merge Left Join (cost=10,000,000,080.50..10,000,000,831.17 rows=901 width=4) (actual time=11.450..12.444 rows=1,044 loops=1)

  • Merge Cond: (i3_.id_ca = i4_.id_ca)
16. 11.219 11.219 ↑ 1.1 409 1

Index Only Scan using pkey_cadre_acquisition on ca i3_ (cost=0.27..736.28 rows=459 width=4) (actual time=10.860..11.219 rows=409 loops=1)

  • Heap Fetches: 214
17. 0.435 0.747 ↓ 1.1 964 1

Sort (cost=10,000,000,080.23..10,000,000,082.48 rows=901 width=4) (actual time=0.585..0.747 rows=964 loops=1)

  • Sort Key: i4_.id_ca
  • Sort Method: quicksort Memory: 70kB
18. 0.312 0.312 ↓ 1.1 964 1

Seq Scan on ca_contact i4_ (cost=10,000,000,000.00..10,000,000,036.01 rows=901 width=4) (actual time=0.013..0.312 rows=964 loops=1)

19. 2.895 3.819 ↓ 3.1 10,385 1

Sort (cost=10,000,000,296.98..10,000,000,305.30 rows=3,328 width=4) (actual time=1.929..3.819 rows=10,385 loops=1)

  • Sort Key: i5_.id_jdd
  • Sort Method: quicksort Memory: 250kB
20. 0.924 0.924 ↑ 1.0 3,279 1

Seq Scan on jdd_contact_donnees i5_ (cost=10,000,000,000.00..10,000,000,102.28 rows=3,328 width=4) (actual time=0.010..0.924 rows=3,279 loops=1)

Total runtime : 14,156.120 ms