explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VDHR

Settings
# exclusive inclusive rows x rows loops node
1. 3,779.788 214,452.695 ↑ 1.1 3,940,075 1

Unique (cost=4,467,876.30..4,697,719.76 rows=4,178,972 width=125) (actual time=204,804.319..214,452.695 rows=3,940,075 loops=1)

2. 37,242.724 210,672.907 ↑ 1.1 3,940,075 1

Sort (cost=4,467,876.30..4,478,323.73 rows=4,178,972 width=125) (actual time=204,804.317..210,672.907 rows=3,940,075 loops=1)

  • Sort Key: (COALESCE((avpm.areaid)::integer, '-1'::integer)), eh.employeeid, eh.startdate, eh.enddate, etm.effectivedate, etm.enddate, eh.employeetypeid, eh.statusid, eh.partorfulltime, eh.levelid, t.teamid, t.teamdescription, eh.geographiclocationid, gl.geographiclocationdescription, t.virtualcenterid, vc.virtualcenterdescription, eh.peoplesoftstatusid, eh.employeeversionnumber, (COALESCE(t.virtualcenterid, eh.geographiclocationid)), (COALESCE(((vc.virtualcenterdescription)::text || ' [Virtual] '::text), (gl.geographiclocationdescription)::text)), (CASE WHEN (t.virtualcenterid IS NULL) THEN 0 ELSE 1 END)
  • Sort Method: external merge Disk: 390680kB
3. 2,423.659 173,430.183 ↑ 1.1 3,940,075 1

Hash Left Join (cost=5,401.98..3,465,519.77 rows=4,178,972 width=125) (actual time=1,098.340..173,430.183 rows=3,940,075 loops=1)

  • Hash Cond: (eh.geographiclocationid = gl.geographiclocationid)
4. 1,569.772 171,005.917 ↑ 1.1 3,940,075 1

Hash Left Join (cost=5,378.27..3,433,512.04 rows=4,178,972 width=72) (actual time=1,097.671..171,005.917 rows=3,940,075 loops=1)

  • Hash Cond: ((avpm.areaid = vc.areaid) AND (t.virtualcenterid = vc.virtualcenterid))
5. 2,172.458 169,436.028 ↑ 1.1 3,940,075 1

Hash Left Join (cost=5,376.90..3,411,537.95 rows=4,178,972 width=63) (actual time=1,097.395..169,436.028 rows=3,940,075 loops=1)

  • Hash Cond: ((etm.teamid = t.teamid) AND (avpm.areaid = t.areaid))
6. 6,941.939 167,230.344 ↑ 1.1 3,940,075 1

Hash Left Join (cost=5,345.32..3,389,541.67 rows=4,178,972 width=43) (actual time=1,064.130..167,230.344 rows=3,940,075 loops=1)

  • Hash Cond: ((eh.employeeid)::text = (etm.employeeid)::text)
  • Join Filter: (((etm.effectivedate >= eh.startdate) AND (etm.effectivedate <= eh.enddate) AND (etm.enddate >= eh.startdate) AND (etm.enddate <= eh.enddate)) OR ((eh.startdate >= etm.effectivedate) AND (eh.startdate <= etm.enddate)) OR ((eh.enddate >= etm.effectivedate) AND (eh.enddate <= etm.enddate)))
  • Rows Removed by Join Filter: 6020710
7. 16,472.495 159,279.403 ↑ 1.1 3,898,969 1

Nested Loop Left Join (cost=1.15..2,864,681.96 rows=4,178,972 width=31) (actual time=2.080..159,279.403 rows=3,898,969 loops=1)

  • Join Filter: ((((eh.level32id)::text = (avpm.employeeid)::text) AND (NOT a.issvparea)) OR ((eh.level32id IS NULL) AND ((eh.level34id)::text = (avpm.employeeid)::text) AND a.issvparea AND (((avpm.effectivedate >= eh.startdate) AND (avpm.effectivedate <= eh.enddate) AND (avpm.enddate >= eh.startdate) AND (avpm.enddate <= eh.enddate)) OR ((eh.startdate >= avpm.effectivedate) AND (eh.startdate <= avpm.enddate)) OR ((eh.enddate >= avpm.effectivedate) AND (eh.enddate <= avpm.enddate)))))
  • Rows Removed by Join Filter: 45604593
8. 139,527.889 139,527.889 ↑ 1.3 3,279,019 1

Seq Scan on tblemployeehistory eh (cost=0.00..827,430.72 rows=4,178,972 width=43) (actual time=2.021..139,527.889 rows=3,279,019 loops=1)

9. 3,278.952 3,279.019 ↓ 1.2 15 3,279,019

Materialize (cost=1.15..2.42 rows=13 width=18) (actual time=0.000..0.001 rows=15 loops=3,279,019)

10. 0.025 0.067 ↓ 1.2 15 1

Hash Join (cost=1.15..2.36 rows=13 width=18) (actual time=0.053..0.067 rows=15 loops=1)

  • Hash Cond: (avpm.areaid = a.areaid)
11. 0.007 0.007 ↑ 1.0 15 1

Seq Scan on tblareavpmapping avpm (cost=0.00..1.15 rows=15 width=17) (actual time=0.003..0.007 rows=15 loops=1)

12. 0.028 0.035 ↓ 1.2 7 1

Hash (cost=1.07..1.07 rows=6 width=3) (actual time=0.035..0.035 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.007 0.007 ↓ 1.2 7 1

Seq Scan on tblarea a (cost=0.00..1.07 rows=6 width=3) (actual time=0.003..0.007 rows=7 loops=1)

  • Filter: ((NOT issvparea) OR issvparea)
14. 44.348 1,009.002 ↑ 1.0 116,808 1

Hash (cost=3,199.08..3,199.08 rows=116,808 width=19) (actual time=1,009.002..1,009.002 rows=116,808 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3508kB
15. 964.654 964.654 ↑ 1.0 116,808 1

Seq Scan on tblemployeeteammapping etm (cost=0.00..3,199.08 rows=116,808 width=19) (actual time=30.549..964.654 rows=116,808 loops=1)

16. 0.207 33.226 ↓ 1.0 385 1

Hash (cost=25.83..25.83 rows=383 width=26) (actual time=33.226..33.226 rows=385 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
17. 33.019 33.019 ↓ 1.0 385 1

Seq Scan on tblteam t (cost=0.00..25.83 rows=383 width=26) (actual time=2.377..33.019 rows=385 loops=1)

18. 0.055 0.117 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=13) (actual time=0.117..0.117 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.062 0.062 ↑ 1.0 15 1

Seq Scan on tblvirtualcenter vc (cost=0.00..1.15 rows=15 width=13) (actual time=0.057..0.062 rows=15 loops=1)

20. 0.263 0.607 ↑ 1.0 387 1

Hash (cost=18.87..18.87 rows=387 width=15) (actual time=0.607..0.607 rows=387 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
21. 0.344 0.344 ↑ 1.0 387 1

Seq Scan on tblgeographiclocation gl (cost=0.00..18.87 rows=387 width=15) (actual time=0.059..0.344 rows=387 loops=1)

Planning time : 14.364 ms