explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Eizs

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,917.344 ↑ 1.0 10 1

Limit (cost=36,232.44..36,232.47 rows=10 width=443) (actual time=2,917.335..2,917.344 rows=10 loops=1)

2.          

Initplan (for Limit)

3. 0.009 0.009 ↑ 1,360.0 1 1

Seq Scan on exercice_apres (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.009..0.009 rows=1 loops=1)

4. 0.003 0.003 ↑ 1,360.0 1 1

Seq Scan on exercice_apres exercice_apres_1 (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.003..0.003 rows=1 loops=1)

5. 0.953 2,917.333 ↑ 1.9 10 1

Sort (cost=36,185.24..36,185.29 rows=19 width=443) (actual time=2,917.333..2,917.333 rows=10 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, projet.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: top-N heapsort Memory: 32kB
6. 80.817 2,916.380 ↓ 20.4 388 1

Hash Join (cost=14,036.49..36,184.84 rows=19 width=443) (actual time=2,779.710..2,916.380 rows=388 loops=1)

  • Hash Cond: (sm001000_pi4_cout_annuel_realisationbase.sm001000_pi2_suivi_travauxid = sm001000_pi2_suivi_travauxbase.sm001000_pi2_suivi_travauxid)
7. 73.342 73.342 ↑ 1.0 402,302 1

Seq Scan on sm001000_pi4_cout_annuel_realisationbase (cost=0.00..20,639.31 rows=402,331 width=158) (actual time=0.014..73.342 rows=402,302 loops=1)

8. 0.069 2,762.221 ↓ 32.0 32 1

Hash (cost=14,036.47..14,036.47 rows=1 width=251) (actual time=2,762.221..2,762.221 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
9. 0.308 2,762.152 ↓ 32.0 32 1

Nested Loop (cost=7,301.59..14,036.47 rows=1 width=251) (actual time=117.063..2,762.152 rows=32 loops=1)

10. 30.331 2,759.144 ↓ 100.0 100 1

Nested Loop (cost=7,301.30..13,981.78 rows=1 width=235) (actual time=51.426..2,759.144 rows=100 loops=1)

  • Join Filter: (projet.sm001000_pi_ni_orgn_reqr = accountbase.accountid)
  • Rows Removed by Join Filter: 393,500
11. 0.408 1.713 ↓ 100.0 100 1

Nested Loop Left Join (cost=22.53..46.80 rows=1 width=212) (actual time=0.121..1.713 rows=100 loops=1)

  • Filter: ((projet.sm001000_pi1_sous_voletid = sm001000_pi1_sous_voletbase.sm001000_pi1_sous_voletid) OR (projet.sm001000_pi1_sous_voletid IS NULL))
  • Rows Removed by Filter: 105
12. 0.338 0.505 ↓ 50.0 100 1

Hash Join (cost=22.39..44.54 rows=2 width=180) (actual time=0.108..0.505 rows=100 loops=1)

  • Hash Cond: (projet.sm001000_pi1_programmeid = sm001000_pi1_programmebase.sm001000_pi1_programmeid)
  • Join Filter: ((projet.sm001000_pi1_voletid = sm001000_pi1_voletbase.sm001000_pi1_voletid) OR (projet.sm001000_pi1_voletid IS NULL))
  • Rows Removed by Join Filter: 232
13. 0.070 0.070 ↑ 7.2 100 1

Seq Scan on projet (cost=0.00..17.20 rows=720 width=84) (actual time=0.006..0.070 rows=100 loops=1)

14. 0.008 0.097 ↑ 1.2 40 1

Hash (cost=21.77..21.77 rows=50 width=128) (actual time=0.097..0.097 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
15. 0.040 0.089 ↑ 1.2 40 1

Hash Right Join (cost=11.12..21.77 rows=50 width=128) (actual time=0.047..0.089 rows=40 loops=1)

  • Hash Cond: (sm001000_pi1_voletbase.sm001000_pi1_programmeid = sm001000_pi1_programmebase.sm001000_pi1_programmeid)
16. 0.011 0.011 ↑ 1.4 37 1

Seq Scan on sm001000_pi1_voletbase (cost=0.00..10.50 rows=50 width=240) (actual time=0.003..0.011 rows=37 loops=1)

17. 0.009 0.038 ↑ 2.2 23 1

Hash (cost=10.50..10.50 rows=50 width=288) (actual time=0.038..0.038 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
18. 0.029 0.029 ↑ 2.2 23 1

Seq Scan on sm001000_pi1_programmebase (cost=0.00..10.50 rows=50 width=288) (actual time=0.006..0.029 rows=23 loops=1)

19. 0.800 0.800 ↓ 2.0 2 100

Index Scan using ndx_for_cascaderelationship_sm001000_pi1_renferme00000 on sm001000_pi1_sous_voletbase (cost=0.14..1.12 rows=1 width=240) (actual time=0.005..0.008 rows=2 loops=100)

  • Index Cond: (sm001000_pi1_voletid = sm001000_pi1_voletbase.sm001000_pi1_voletid)
20. 765.997 2,727.100 ↑ 1.0 3,936 100

Hash Right Join (cost=7,278.77..13,885.55 rows=3,954 width=39) (actual time=0.353..27.271 rows=3,936 loops=100)

  • Hash Cond: (xxaddress2.parentid = accountbase.accountid)
21. 1,927.200 1,927.200 ↓ 1.0 67,404 100

Seq Scan on customeraddressbase xxaddress2 (cost=0.00..6,317.24 rows=66,807 width=16) (actual time=0.003..19.272 rows=67,404 loops=100)

  • Filter: (addressnumber = 2)
  • Rows Removed by Filter: 67,404
22. 0.734 33.903 ↑ 1.0 3,936 1

Hash (cost=7,229.34..7,229.34 rows=3,954 width=39) (actual time=33.903..33.903 rows=3,936 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 307kB
23. 7.932 33.169 ↑ 1.0 3,936 1

Hash Right Join (cost=733.56..7,229.34 rows=3,954 width=39) (actual time=5.071..33.169 rows=3,936 loops=1)

  • Hash Cond: (xxaddress1.parentid = accountbase.accountid)
24. 20.182 20.182 ↑ 1.0 67,404 1

Seq Scan on customeraddressbase xxaddress1 (cost=0.00..6,317.24 rows=68,012 width=16) (actual time=0.005..20.182 rows=67,404 loops=1)

  • Filter: (addressnumber = 1)
  • Rows Removed by Filter: 67,404
25. 1.314 5.055 ↑ 1.0 3,936 1

Hash (cost=684.36..684.36 rows=3,936 width=583) (actual time=5.055..5.055 rows=3,936 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 962kB
26. 3.741 3.741 ↑ 1.0 3,936 1

Seq Scan on accountbase (cost=0.00..684.36 rows=3,936 width=583) (actual time=0.025..3.741 rows=3,936 loops=1)

27. 1.049 2.700 ↓ 0.0 0 100

Index Scan using ndx_pi2_suivi_travauxbase_pi2_projet_infrastructureid on sm001000_pi2_suivi_travauxbase (cost=0.29..54.69 rows=1 width=180) (actual time=0.023..0.027 rows=0 loops=100)

  • Index Cond: (sm001000_pi2_projet_infrastructureid = projet.sm001000_pi2_projet_infrastructureid)
  • Filter: ((sm001000_pi_s_suiv_trav = 20) AND ((SubPlan 3) = sm001000_pi_n_vers))
  • Rows Removed by Filter: 1
28.          

SubPlan (for Index Scan)

29. 0.508 1.651 ↑ 1.0 1 127

Aggregate (cost=15.90..15.91 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=127)

30. 0.762 1.143 ↓ 2.0 4 127

Bitmap Heap Scan on sm001000_pi2_suivi_travauxbase sm001000_pi2_suivi_travauxbase_1 (cost=4.31..15.90 rows=2 width=4) (actual time=0.006..0.009 rows=4 loops=127)

  • Recheck Cond: (sm001000_pi2_projet_infrastructureid = projet.sm001000_pi2_projet_infrastructureid)
  • Filter: (sm001000_pi_d_vers < '2019-10-01 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=619
31. 0.381 0.381 ↓ 1.7 5 127

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=5 loops=127)

  • Index Cond: (sm001000_pi2_projet_infrastructureid = projet.sm001000_pi2_projet_infrastructureid)