explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4EvP : phhnygen3_1

Settings
# exclusive inclusive rows x rows loops node
1. 0.405 15,155,107.343 ↓ 158.0 158 1

Sort (cost=2,368,737.04..2,368,737.04 rows=1 width=62) (actual time=15,155,107.303..15,155,107.343 rows=158 loops=1)

  • Output: ((ui.lastname)::character varying(50)), ((ui.firstname)::character varying(50)), dmv_timesheetday_facts7.userduplicatename2, ((login.loginname)::character varying(255)), dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1
  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts7.userduplicatename2, ((login.loginname)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 46kB
  • Buffers: shared hit=109871204 read=163864
2.          

CTE dmv_timesheetday_facts0cte

3. 9.308 15,155,102.236 ↓ 446.0 446 1

HashAggregate (cost=2,368,733.82..2,368,733.83 rows=1 width=21) (actual time=15,155,102.001..15,155,102.236 rows=446 loops=1)

  • Output: sum((CASE WHEN ((at.timeoffcodeid IS NULL) AND phhnygen3.timeallocationisbillableordefault(isbillable."boolean", at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)), '1': (...)
  • Group Key: ts.userid, ui_1.duplicatename
  • Buffers: shared hit=109866816 read=163864
4. 520,928.996 15,155,092.928 ↓ 588.6 9,418 1

Nested Loop (cost=90,134.05..2,368,733.70 rows=16 width=21) (actual time=29,459.356..15,155,092.928 rows=9,418 loops=1)

  • Output: ts.userid, (CASE WHEN ((at.timeoffcodeid IS NULL) AND phhnygen3.timeallocationisbillableordefault(isbillable."boolean", at.projectid)) THEN (at.duration * (COALESCE(((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::nu (...)
  • Join Filter: (ts.id = at.timesheetid)
  • Rows Removed by Join Filter: 1235678262
  • Buffers: shared hit=109866816 read=163864
5. 4.243 30.812 ↓ 640.0 640 1

Nested Loop (cost=45.78..1,990.11 rows=1 width=21) (actual time=0.561..30.812 rows=640 loops=1)

  • Output: ts.userid, ts.id, ui_1.duplicatename
  • Buffers: shared hit=2180 read=344
6. 17.982 18.249 ↓ 640.0 640 1

Index Scan using uix2tsuseridstartdate on phhnygen3.timesheet ts (cost=45.50..1,987.81 rows=1 width=20) (actual time=0.538..18.249 rows=640 loops=1)

  • Output: ts.id, ts.userid, ts.startdate, ts.enddate, ts.approvalstatus, ts.duedate, ts.autosubmitdatetimeutc, ts.lastautosubmitattemptdatetimeutc, ts.createdonutc
  • Index Cond: ((ts.startdate >= '2019-05-12'::date) AND (ts.startdate <= '2019-05-18'::date))
  • Filter: ((NOT (hashed SubPlan 3)) AND (ts.enddate >= '2019-05-12'::date) AND (ts.enddate <= '2019-05-18'::date) AND ((hashed SubPlan 1) OR (NOT (hashed SubPlan 2))))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=238 read=344
7.          

SubPlan (forIndex Scan)

8. 0.001 0.001 ↓ 0.0 0 1

Index Only Scan using ix4ud_userdivisionstartend on phhnygen3.userdivision userdivision5 (cost=0.15..23.71 rows=126 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: userdivision5.userid
  • Index Cond: ((userdivision5.startdate <= '2019-05-23'::date) AND (userdivision5.enddate >= '2019-05-23'::date))
  • Heap Fetches: 0
  • Buffers: shared hit=1
9. 0.146 0.146 ↓ 1.0 283 1

Seq Scan on phhnygen3.userlocation userlocation3 (cost=0.00..10.41 rows=277 width=4) (actual time=0.011..0.146 rows=283 loops=1)

  • Output: userlocation3.userid
  • Filter: ((userlocation3.startdate <= '2019-05-23'::date) AND (userlocation3.enddate >= '2019-05-23'::date) AND (userlocation3.locationid = ANY ('{f90224c8-82cd-43ab-867c-4241074d5168,aa631d7e-0124-4688-ad2d-7152b1e8f171, (...)
  • Rows Removed by Filter: 67
  • Buffers: shared hit=4
10. 0.120 0.120 ↓ 1.0 336 1

Seq Scan on phhnygen3.userlocation userlocation4 (cost=0.00..9.13 rows=328 width=4) (actual time=0.004..0.120 rows=336 loops=1)

  • Output: userlocation4.userid
  • Filter: ((userlocation4.startdate <= '2019-05-23'::date) AND (userlocation4.enddate >= '2019-05-23'::date))
  • Rows Removed by Filter: 14
  • Buffers: shared hit=4
11. 8.320 8.320 ↑ 1.0 1 640

Index Scan using userinfo_pkey on phhnygen3.userinfo ui_1 (cost=0.28..2.29 rows=1 width=5) (actual time=0.010..0.013 rows=1 loops=640)

  • Output: ui_1.id, ui_1.firstname, ui_1.lastname, ui_1.email, ui_1.startdate, ui_1.enddate, ui_1.externalid, ui_1.disabled, ui_1.info1, ui_1.info2, ui_1.info3, ui_1.info4, ui_1.info5, ui_1.info6, ui_1.info7, ui_1.info8, ui_1.info9 (...)
  • Index Cond: (ui_1.id = ts.userid)
  • Buffers: shared hit=1942
12. 4,827,667.843 14,634,133.120 ↓ 1.0 1,930,762 640

Hash Left Join (cost=90,088.27..2,324,434.38 rows=1,880,409 width=89) (actual time=5.137..22,865.833 rows=1,930,762 loops=640)

  • Output: NULL::uuid, NULL::integer, NULL::date, NULL::uuid, NULL::interval, NULL::text, NULL::integer, NULL::date, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::uuid, NULL::interval, NULL::interval, NULL::interval, (...)
  • Hash Cond: ((at.projectid = pj_1.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=109864636 read=163520
13. 2,631,084.826 9,806,464.000 ↓ 1.0 1,930,762 640

Hash Left Join (cost=89,879.58..1,341,711.98 rows=1,880,409 width=97) (actual time=5.105..15,322.600 rows=1,930,762 loops=640)

  • Output: at.timeoffcodeid, at.projectid, at.duration, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid, isbillable."boolean", ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Hash Cond: (at.projectid = pj.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Filter: (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END = ANY ('{3,63,317,57,512,318,316,397,399,523,7,479,488,460,466,462,392,447,304,419,409,213,467,143,514,420,43,4,489,526 (...)
  • Rows Removed by Filter: 466822
  • Buffers: shared hit=109864465 read=163519
14. 1,423,846.589 7,175,368.320 ↓ 1.0 2,397,584 640

Hash Right Join (cost=89,417.60..392,946.26 rows=2,397,582 width=65) (actual time=5.081..11,211.513 rows=2,397,584 loops=640)

  • Output: at.timeoffcodeid, at.projectid, at.duration, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid, isbillable."boolean
  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=109864276 read=163519
15. 5,749,306.240 5,749,306.240 ↓ 53.8 2,348,518 640

Seq Scan on phhnygen3.dm_attendancetimeallocation_metadata isbillable (cost=0.00..302,873.61 rows=43,670 width=17) (actual time=1.587..8,983.291 rows=2,348,518 loops=640)

  • Output: isbillable.id, isbillable.factid, isbillable.parentid, isbillable.index, isbillable.key, isbillable.uri, isbillable.slug, isbillable."boolean", isbillable.date, isbillable.number, isbillable.text, isbillable. (...)
  • Filter: (upper(isbillable.key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 6385534
  • Buffers: shared hit=109828959 read=163361
16. 1,198.792 2,215.491 ↓ 1.0 2,397,584 1

Hash (cost=59,447.82..59,447.82 rows=2,397,582 width=80) (actual time=2,215.491..2,215.491 rows=2,397,584 loops=1)

  • Output: at.timeoffcodeid, at.projectid, at.duration, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid, at.id
  • Buckets: 4194304 Batches: 1 Memory Usage: 248069kB
  • Buffers: shared hit=35314 read=158
17. 1,016.699 1,016.699 ↓ 1.0 2,397,584 1

Seq Scan on phhnygen3.dm_attendancetimeallocation_facts at (cost=0.00..59,447.82 rows=2,397,582 width=80) (actual time=0.019..1,016.699 rows=2,397,584 loops=1)

  • Output: at.timeoffcodeid, at.projectid, at.duration, at.breaktypeid, at.timesheetid, at.entrydate, at.userspecifiedclientid, at.id
  • Buffers: shared hit=35314 read=158
18. 1.580 10.854 ↑ 1.0 4,407 1

Hash (cost=406.62..406.62 rows=4,429 width=52) (actual time=10.854..10.854 rows=4,407 loops=1)

  • Output: pj.id, pj.clientbillingallocationmethod, pc.effectivedate, pc.enddate, pc.clientid, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 8192 Batches: 1 Memory Usage: 310kB
  • Buffers: shared hit=189
19. 2.646 9.274 ↑ 1.0 4,407 1

Hash Join (cost=139.36..406.62 rows=4,429 width=52) (actual time=4.093..9.274 rows=4,407 loops=1)

  • Output: pj.id, pj.clientbillingallocationmethod, pc.effectivedate, pc.enddate, pc.clientid, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Hash Cond: (pj.id = pc.projectid)
  • Buffers: shared hit=189
20. 2.587 2.587 ↑ 1.0 4,429 1

Seq Scan on phhnygen3.project pj (cost=0.00..206.36 rows=4,429 width=8) (actual time=0.011..2.587 rows=4,429 loops=1)

  • Output: pj.id, pj.timeentrycappercent, pj.info1, pj.info2, pj.info3, pj.info4, pj.info5, pj.info6, pj.info7, pj.info8, pj.info9, pj.info10, pj.info11, pj.info12, pj.info13, pj.info14, pj.info15, pj.info16, pj.i (...)
  • Filter: (pj.clientbillingallocationmethod = 0)
  • Buffers: shared hit=151
21. 1.518 4.041 ↑ 1.0 4,407 1

Hash (cost=83.05..83.05 rows=4,505 width=48) (actual time=4.041..4.041 rows=4,407 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, pc.clientid, ((COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric))
  • Buckets: 8192 Batches: 1 Memory Usage: 293kB
  • Buffers: shared hit=38
22. 2.523 2.523 ↑ 1.0 4,407 1

Seq Scan on phhnygen3.projectclient pc (cost=0.00..83.05 rows=4,505 width=48) (actual time=0.015..2.523 rows=4,407 loops=1)

  • Output: pc.projectid, pc.effectivedate, pc.enddate, pc.clientid, (COALESCE(pc.costallocationpercentage, '100'::numeric) / '100'::numeric)
  • Buffers: shared hit=38
23. 0.000 1.277 ↓ 0.0 0 1

Hash (cost=208.67..208.67 rows=1 width=16) (actual time=1.277..1.277 rows=0 loops=1)

  • Output: pj_1.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=151
24. 0.002 1.277 ↓ 0.0 0 1

Nested Loop (cost=0.28..208.67 rows=1 width=16) (actual time=1.277..1.277 rows=0 loops=1)

  • Output: pj_1.id, pc_1.effectivedate, pc_1.enddate, pc_1.clientid
  • Buffers: shared hit=151
25. 1.275 1.275 ↓ 0.0 0 1

Seq Scan on phhnygen3.project pj_1 (cost=0.00..206.36 rows=1 width=4) (actual time=1.275..1.275 rows=0 loops=1)

  • Output: pj_1.id, pj_1.timeentrycappercent, pj_1.info1, pj_1.info2, pj_1.info3, pj_1.info4, pj_1.info5, pj_1.info6, pj_1.info7, pj_1.info8, pj_1.info9, pj_1.info10, pj_1.info11, pj_1.info12, pj_1.info13, pj_1.info14, (...)
  • Filter: (pj_1.clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 4429
  • Buffers: shared hit=151
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on phhnygen3.projectclient pc_1 (cost=0.28..2.30 rows=1 width=16) (never executed)

  • Output: pc_1.projectid, pc_1.clientid, pc_1.effectivedate, pc_1.enddate
  • Index Cond: (pc_1.projectid = pj_1.id)
  • Heap Fetches: 0
27. 0.255 15,155,106.938 ↓ 158.0 158 1

Nested Loop (cost=0.98..3.20 rows=1 width=62) (actual time=15,155,102.178..15,155,106.938 rows=158 loops=1)

  • Output: ui.lastname, ui.firstname, dmv_timesheetday_facts7.userduplicatename2, login.loginname, dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1
  • Buffers: shared hit=109871192 read=163864
28. 0.318 15,155,106.525 ↓ 158.0 158 1

Nested Loop (cost=0.83..3.02 rows=1 width=66) (actual time=15,155,102.148..15,155,106.525 rows=158 loops=1)

  • Output: dmv_timesheetday_facts7.userduplicatename2, dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1, ui.lastname, ui.firstname, login.loginname, du.departmentid
  • Join Filter: (dmv_timesheetday_facts7.userid1 = du.userid)
  • Buffers: shared hit=109870876 read=163864
29. 0.597 15,155,105.315 ↓ 446.0 446 1

Nested Loop (cost=0.56..2.66 rows=1 width=70) (actual time=15,155,102.056..15,155,105.315 rows=446 loops=1)

  • Output: dmv_timesheetday_facts7.userduplicatename2, dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1, ui.lastname, ui.firstname, ui.id, login.loginname, login.userid
  • Join Filter: (dmv_timesheetday_facts7.userid1 = login.userid)
  • Buffers: shared hit=109869532 read=163864
30. 0.400 15,155,103.826 ↓ 446.0 446 1

Nested Loop (cost=0.28..2.32 rows=1 width=38) (actual time=15,155,102.024..15,155,103.826 rows=446 loops=1)

  • Output: dmv_timesheetday_facts7.userduplicatename2, dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1, ui.lastname, ui.firstname, ui.id
  • Buffers: shared hit=109868173 read=163864
31. 15,155,102.534 15,155,102.534 ↓ 446.0 446 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts7 (cost=0.00..0.02 rows=1 width=21) (actual time=15,155,102.006..15,155,102.534 rows=446 loops=1)

  • Output: dmv_timesheetday_facts7.billableduration0, dmv_timesheetday_facts7.userid1, dmv_timesheetday_facts7.userduplicatename2
  • Buffers: shared hit=109866816 read=163864
32. 0.892 0.892 ↑ 1.0 1 446

Index Scan using userinfo_pkey on phhnygen3.userinfo ui (cost=0.28..2.29 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=446)

  • Output: ui.id, ui.firstname, ui.lastname, ui.email, ui.startdate, ui.enddate, ui.externalid, ui.disabled, ui.info1, ui.info2, ui.info3, ui.info4, ui.info5, ui.info6, ui.info7, ui.info8, ui.info9, ui.info10, ui.info11, ui.inf (...)
  • Index Cond: (ui.id = dmv_timesheetday_facts7.userid1)
  • Buffers: shared hit=1357
33. 0.892 0.892 ↑ 1.0 1 446

Index Scan using login_pkey on phhnygen3.login (cost=0.28..0.32 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=446)

  • Output: login.loginname, login.userid
  • Index Cond: (login.userid = ui.id)
  • Buffers: shared hit=1359
34. 0.892 0.892 ↓ 0.0 0 446

Index Scan using ixduuserid on phhnygen3.departmentusers du (cost=0.28..0.35 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=446)

  • Output: du.departmentid, du.userid, du.isprimarydepartment
  • Index Cond: (du.userid = login.userid)
  • Filter: (du.departmentid = ANY ('{69,67,70,87,71,72,74,75,73,77,76,78,104,106,107,108,109,112,105,110,111,68,79,88,80,81,83,84,82,95,85,86}'::integer[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1344
35. 0.158 0.158 ↑ 1.0 1 158

Index Only Scan using departments_pkey on phhnygen3.departments dep (cost=0.14..0.17 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=158)

  • Output: dep.id
  • Index Cond: (dep.id = du.departmentid)
  • Heap Fetches: 158
  • Buffers: shared hit=316
Planning time : 7.825 ms