explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7bGq

Settings
# exclusive inclusive rows x rows loops node
1. 1,222.006 2,268.262 ↓ 2,447.7 44,058 1

Sort (cost=39,113.32..39,113.36 rows=18 width=391) (actual time=2,088.270..2,268.262 rows=44,058 loops=1)

  • Sort Key: programme.sm001000_pi_nm_acrn_prog, volet.sm001000_pi_c_acrn_volt, sous_volet.sm001000_pi_c_acrn_sous_volt, projet.sm001000_pi_n_doss, suivi_travaux.sm001000_pi2_suivi_travauxid, smar.sm001000_pi_d_aa_exer_finn
  • Sort Method: external merge Disk: 10,216kB
2. 176.697 1,046.256 ↓ 2,447.7 44,058 1

Hash Join (cost=14,314.95..39,112.94 rows=18 width=391) (actual time=444.885..1,046.256 rows=44,058 loops=1)

  • Hash Cond: (smar.sm001000_pi2_suivi_travauxid = suivi_travaux.sm001000_pi2_suivi_travauxid)
3. 424.987 424.987 ↑ 1.0 448,991 1

Seq Scan on sm001000_pi4_cout_annuel_realisationbase smar (cost=0.00..23,113.97 rows=448,997 width=40) (actual time=0.012..424.987 rows=448,991 loops=1)

4. 2.315 444.572 ↓ 3,901.0 3,901 1

Hash (cost=14,314.94..14,314.94 rows=1 width=221) (actual time=444.572..444.572 rows=3,901 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 824kB
5. 5.184 442.257 ↓ 3,901.0 3,901 1

Nested Loop (cost=7,581.74..14,314.94 rows=1 width=221) (actual time=186.835..442.257 rows=3,901 loops=1)

6. 16.593 401.964 ↓ 3,901.0 3,901 1

Merge Join (cost=7,581.46..14,314.61 rows=1 width=198) (actual time=177.180..401.964 rows=3,901 loops=1)

  • Merge Cond: (suivi_travaux.sm001000_pi2_projet_infrastructureid = projet.sm001000_pi2_projet_infrastructureid)
  • Join Filter: ((SubPlan 1) = suivi_travaux.sm001000_pi_n_vers)
  • Rows Removed by Join Filter: 9,778
7. 73.856 73.856 ↑ 1.8 13,680 1

Index Scan using ndx_pi2_suivi_travauxbase_pi2_projet_infrastructureid on sm001000_pi2_suivi_travauxbase suivi_travaux (cost=0.29..6,109.29 rows=24,286 width=36) (actual time=0.011..73.856 rows=13,680 loops=1)

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

Sort (cost=7,581.17..7,582.78 rows=644 width=182) (actual time=176.990..188.404 rows=52,005 loops=1)

  • Sort Key: projet.sm001000_pi2_projet_infrastructureid
  • Sort Method: external sort Disk: 6,976kB
9. 16.990 116.793 ↓ 67.1 43,206 1

Hash Left Join (cost=14.66..7,551.13 rows=644 width=182) (actual time=0.175..116.793 rows=43,206 loops=1)

  • Hash Cond: (volet.sm001000_pi1_voletid = sous_volet.sm001000_pi1_voletid)
  • Filter: ((projet.sm001000_pi1_sous_voletid = sous_volet.sm001000_pi1_sous_voletid) OR (projet.sm001000_pi1_sous_voletid IS NULL))
  • Rows Removed by Filter: 42,288
10. 22.154 99.733 ↓ 24.0 43,206 1

Hash Left Join (cost=6.44..7,499.04 rows=1,800 width=180) (actual time=0.099..99.733 rows=43,206 loops=1)

  • Hash Cond: (programme.sm001000_pi1_programmeid = volet.sm001000_pi1_programmeid)
  • Filter: ((projet.sm001000_pi1_voletid = volet.sm001000_pi1_voletid) OR (projet.sm001000_pi1_voletid IS NULL))
  • Rows Removed by Filter: 84,733
11. 61.176 77.536 ↑ 1.0 43,206 1

Hash Join (cost=2.54..6,793.04 rows=43,206 width=148) (actual time=0.048..77.536 rows=43,206 loops=1)

  • Hash Cond: (projet.sm001000_pi1_programmeid = programme.sm001000_pi1_programmeid)
12. 16.329 16.329 ↑ 1.0 43,206 1

Seq Scan on sm001000_pi2_projet_infrastructurebase projet (cost=0.00..6,657.06 rows=43,206 width=84) (actual time=0.007..16.329 rows=43,206 loops=1)

13. 0.007 0.031 ↓ 1.0 25 1

Hash (cost=2.24..2.24 rows=24 width=80) (actual time=0.031..0.031 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 0.024 0.024 ↓ 1.0 25 1

Seq Scan on sm001000_pi1_programmebase programme (cost=0.00..2.24 rows=24 width=80) (actual time=0.006..0.024 rows=25 loops=1)

15. 0.009 0.043 ↓ 1.0 41 1

Hash (cost=3.40..3.40 rows=40 width=64) (actual time=0.043..0.043 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
16. 0.034 0.034 ↓ 1.0 41 1

Seq Scan on sm001000_pi1_voletbase volet (cost=0.00..3.40 rows=40 width=64) (actual time=0.005..0.034 rows=41 loops=1)

17. 0.011 0.070 ↑ 1.0 54 1

Hash (cost=7.54..7.54 rows=54 width=34) (actual time=0.069..0.070 rows=54 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
18. 0.059 0.059 ↑ 1.0 54 1

Seq Scan on sm001000_pi1_sous_voletbase sous_volet (cost=0.00..7.54 rows=54 width=34) (actual time=0.005..0.059 rows=54 loops=1)

19.          

SubPlan (for Merge Join)

20. 13.679 123.111 ↑ 1.0 1 13,679

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

21. 68.395 109.432 ↑ 1.0 1 13,679

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

  • Recheck Cond: (sm001000_pi2_projet_infrastructureid = projet.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=50,323
22. 41.037 41.037 ↓ 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.003..0.003 rows=4 loops=13,679)

  • Index Cond: (sm001000_pi2_projet_infrastructureid = projet.sm001000_pi2_projet_infrastructureid)
23. 35.109 35.109 ↑ 1.0 1 3,901

Index Scan using pk_2073058421_cndx_primarykey_account on accountbase organisme (cost=0.28..0.33 rows=1 width=39) (actual time=0.009..0.009 rows=1 loops=3,901)

  • Index Cond: (accountid = projet.sm001000_pi_ni_orgn_reqr)