explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zV3g

Settings
# exclusive inclusive rows x rows loops node
1. 1,198.177 109,518.060 ↓ 2,447.7 44,058 1

Sort (cost=50,845.54..50,845.59 rows=18 width=391) (actual time=109,335.840..109,518.060 rows=44,058 loops=1)

  • Sort Key: sm001000_pi1_programmebase.sm001000_pi_nm_acrn_prog, sm001000_pi1_voletbase.sm001000_pi_c_acrn_volt, sm001000_pi1_sous_voletbase.sm001000_pi_c_acrn_sous_volt, sm001000_pi2_projet_infrastructurebase.sm001000_pi_n_doss, sm001000_pi2_suivi_travauxbase.sm001000_pi2_suivi_travauxid, sm001000_pi4_cout_annuel_realisationbase.sm001000_pi_d_aa_exer_finn
  • Sort Method: external merge Disk: 10216kB
2. 178.837 108,319.883 ↓ 2,447.7 44,058 1

Hash Join (cost=26,047.18..50,845.17 rows=18 width=391) (actual time=107,674.413..108,319.883 rows=44,058 loops=1)

  • Hash Cond: (sm001000_pi4_cout_annuel_realisationbase.sm001000_pi2_suivi_travauxid = sm001000_pi2_suivi_travauxbase.sm001000_pi2_suivi_travauxid)
3. 466.736 466.736 ↑ 1.0 448,991 1

Seq Scan on sm001000_pi4_cout_annuel_realisationbase (cost=0.00..23,113.97 rows=448,997 width=136) (actual time=0.014..466.736 rows=448,991 loops=1)

4. 13.404 107,674.310 ↓ 3,901.0 3,901 1

Hash (cost=26,047.16..26,047.16 rows=1 width=221) (actual time=107,674.310..107,674.310 rows=3,901 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 824kB
5. 1,224.106 107,660.906 ↓ 3,901.0 3,901 1

Nested Loop (cost=13,737.54..26,047.16 rows=1 width=221) (actual time=228.031..107,660.906 rows=3,901 loops=1)

  • Join Filter: (sm001000_pi2_projet_infrastructurebase.sm001000_pi_ni_orgn_reqr = accountbase.accountid)
  • Rows Removed by Join Filter: 15373841
6. 63.500 1,086.394 ↓ 3,901.0 3,901 1

Merge Join (cost=7,862.19..14,595.34 rows=1 width=198) (actual time=172.854..1,086.394 rows=3,901 loops=1)

  • Merge Cond: (sm001000_pi2_suivi_travauxbase.sm001000_pi2_projet_infrastructureid = sm001000_pi2_projet_infrastructurebase.sm001000_pi2_projet_infrastructureid)
  • Join Filter: ((SubPlan 1) = sm001000_pi2_suivi_travauxbase.sm001000_pi_n_vers)
  • Rows Removed by Join Filter: 9778
7. 308.273 308.273 ↑ 1.8 13,680 1

Index Scan using ndx_pi2_suivi_travauxbase_pi2_projet_infrastructureid on sm001000_pi2_suivi_travauxbase (cost=0.29..6,109.29 rows=24,286 width=180) (actual time=0.014..308.273 rows=13,680 loops=1)

  • Filter: (sm001000_pi_s_suiv_trav = 20)
  • Rows Removed by Filter: 5
8. 100.819 208.498 ↓ 80.8 52,005 1

Sort (cost=7,861.91..7,863.52 rows=644 width=182) (actual time=169.126..208.498 rows=52,005 loops=1)

  • Sort Key: sm001000_pi2_projet_infrastructurebase.sm001000_pi2_projet_infrastructureid
  • Sort Method: external sort Disk: 6976kB
9. 20.214 107.679 ↓ 67.1 43,206 1

Hash Left Join (cost=14.77..7,831.86 rows=644 width=182) (actual time=0.235..107.679 rows=43,206 loops=1)

  • Hash Cond: (sm001000_pi1_voletbase.sm001000_pi1_voletid = sm001000_pi1_sous_voletbase.sm001000_pi1_voletid)
  • Filter: ((sm001000_pi2_projet_infrastructurebase.sm001000_pi1_sous_voletid = sm001000_pi1_sous_voletbase.sm001000_pi1_sous_voletid) OR (sm001000_pi2_projet_infrastructurebase.sm001000_pi1_sous_voletid IS NULL))
  • Rows Removed by Filter: 42288
10. 74.044 87.374 ↓ 24.0 43,206 1

Hash Join (cost=6.56..7,779.77 rows=1,800 width=180) (actual time=0.132..87.374 rows=43,206 loops=1)

  • Hash Cond: (sm001000_pi2_projet_infrastructurebase.sm001000_pi1_programmeid = sm001000_pi1_programmebase.sm001000_pi1_programmeid)
  • Join Filter: ((sm001000_pi2_projet_infrastructurebase.sm001000_pi1_voletid = sm001000_pi1_voletbase.sm001000_pi1_voletid) OR (sm001000_pi2_projet_infrastructurebase.sm001000_pi1_voletid IS NULL))
  • Rows Removed by Join Filter: 84733
11. 13.213 13.213 ↑ 1.0 43,206 1

Seq Scan on sm001000_pi2_projet_infrastructurebase (cost=0.00..6,657.06 rows=43,206 width=468) (actual time=0.004..13.213 rows=43,206 loops=1)

12. 0.013 0.117 ↓ 1.1 44 1

Hash (cost=6.06..6.06 rows=40 width=128) (actual time=0.117..0.117 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.049 0.104 ↓ 1.1 44 1

Hash Right Join (cost=2.54..6.06 rows=40 width=128) (actual time=0.063..0.104 rows=44 loops=1)

  • Hash Cond: (sm001000_pi1_voletbase.sm001000_pi1_programmeid = sm001000_pi1_programmebase.sm001000_pi1_programmeid)
14. 0.011 0.011 ↓ 1.0 41 1

Seq Scan on sm001000_pi1_voletbase (cost=0.00..3.40 rows=40 width=240) (actual time=0.004..0.011 rows=41 loops=1)

15. 0.015 0.044 ↓ 1.0 25 1

Hash (cost=2.24..2.24 rows=24 width=288) (actual time=0.044..0.044 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
16. 0.029 0.029 ↓ 1.0 25 1

Seq Scan on sm001000_pi1_programmebase (cost=0.00..2.24 rows=24 width=288) (actual time=0.009..0.029 rows=25 loops=1)

17. 0.025 0.091 ↑ 1.0 54 1

Hash (cost=7.54..7.54 rows=54 width=210) (actual time=0.091..0.091 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
18. 0.066 0.066 ↑ 1.0 54 1

Seq Scan on sm001000_pi1_sous_voletbase (cost=0.00..7.54 rows=54 width=210) (actual time=0.006..0.066 rows=54 loops=1)

19.          

SubPlan (for Merge Join)

20. 54.716 506.123 ↑ 1.0 1 13,679

Aggregate (cost=15.93..15.94 rows=1 width=4) (actual time=0.037..0.037 rows=1 loops=13,679)

21. 383.012 451.407 ↑ 1.0 1 13,679

Bitmap Heap Scan on sm001000_pi2_suivi_travauxbase sm001000_pi2_suivi_travauxbase_1 (cost=4.31..15.93 rows=1 width=4) (actual time=0.023..0.033 rows=1 loops=13,679)

  • Recheck Cond: (sm001000_pi2_projet_infrastructureid = sm001000_pi2_projet_infrastructurebase.sm001000_pi2_projet_infrastructureid)
  • Filter: (sm001000_pi_d_vers < '2015-01-02 00:00:00-05'::timestamp with time zone)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=50336
22. 68.395 68.395 ↓ 1.3 4 13,679

Bitmap Index Scan on ndx_pi2_suivi_travauxbase_pi2_projet_infrastructureid (cost=0.00..4.31 rows=3 width=0) (actual time=0.005..0.005 rows=4 loops=13,679)

  • Index Cond: (sm001000_pi2_projet_infrastructureid = sm001000_pi2_projet_infrastructurebase.sm001000_pi2_projet_infrastructureid)
23. 31,312.571 105,350.406 ↑ 1.0 3,942 3,901

Hash Right Join (cost=5,875.35..11,402.21 rows=3,969 width=39) (actual time=0.025..27.006 rows=3,942 loops=3,901)

  • Hash Cond: (xxaddress2.parentid = accountbase.accountid)
24. 74,001.970 74,001.970 ↓ 1.0 68,534 3,901

Seq Scan on customeraddressbase xxaddress2 (cost=0.00..5,231.35 rows=68,237 width=16) (actual time=0.004..18.970 rows=68,534 loops=3,901)

  • Filter: (addressnumber = 2)
  • Rows Removed by Filter: 68534
25. 0.739 35.865 ↑ 1.0 3,942 1

Hash (cost=5,825.74..5,825.74 rows=3,969 width=39) (actual time=35.865..35.865 rows=3,942 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 308kB
26. 8.346 35.126 ↑ 1.0 3,942 1

Hash Right Join (cost=413.70..5,825.74 rows=3,969 width=39) (actual time=7.616..35.126 rows=3,942 loops=1)

  • Hash Cond: (xxaddress1.parentid = accountbase.accountid)
27. 19.189 19.189 ↑ 1.0 68,534 1

Seq Scan on customeraddressbase xxaddress1 (cost=0.00..5,231.35 rows=68,831 width=16) (actual time=0.008..19.189 rows=68,534 loops=1)

  • Filter: (addressnumber = 1)
  • Rows Removed by Filter: 68534
28. 3.786 7.591 ↑ 1.0 3,942 1

Hash (cost=364.42..364.42 rows=3,942 width=583) (actual time=7.591..7.591 rows=3,942 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 964kB
29. 3.805 3.805 ↑ 1.0 3,942 1

Seq Scan on accountbase (cost=0.00..364.42 rows=3,942 width=583) (actual time=0.010..3.805 rows=3,942 loops=1)

Planning time : 255.199 ms