explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1A2

Settings
# exclusive inclusive rows x rows loops node
1. 13.227 183.114 ↓ 6.1 1,296 1

Sort (cost=1,235.83..1,236.36 rows=214 width=1,897) (actual time=178.894..183.114 rows=1,296 loops=1)

  • Sort Key: dmv_timesheetday_facts3.entrydate5, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts3.userduplicatename6, ((login.loginname)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((userinfo8.displayname)::text) COLLATE "en_US
  • Sort Method: quicksort Memory: 392kB
  • Buffers: shared hit=215
2.          

CTE dmv_timesheetday_facts0cte

3. 15.211 114.616 ↑ 1.1 1,296 1

HashAggregate (cost=1,168.18..1,182.47 rows=1,429 width=81) (actual time=108.994..114.616 rows=1,296 loops=1)

  • Group Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Buffers: shared hit=208
4. 10.220 99.405 ↑ 1.0 1,429 1

Hash Join (cost=74.14..1,136.02 rows=1,429 width=81) (actual time=18.522..99.405 rows=1,429 loops=1)

  • Hash Cond: (ts.userid = ui_1.id)
  • Buffers: shared hit=208
5. 12.570 88.937 ↑ 1.0 1,429 1

Hash Join (cost=72.46..1,130.06 rows=1,429 width=80) (actual time=18.257..88.937 rows=1,429 loops=1)

  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=207
6. 27.581 74.127 ↑ 1.0 1,463 1

Hash Left Join (cost=61.28..1,100.37 rows=1,463 width=616) (actual time=15.988..74.127 rows=1,463 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=203
7. 9.164 45.916 ↑ 1.0 1,463 1

Hash Left Join (cost=57.57..326.15 rows=1,463 width=65) (actual time=15.296..45.916 rows=1,463 loops=1)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=200
8. 8.958 36.651 ↑ 1.0 1,463 1

Hash Right Join (cost=53.92..311.51 rows=1,463 width=65) (actual time=15.168..36.651 rows=1,463 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=197
9. 16.556 16.556 ↓ 37.1 1,002 1

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..257.52 rows=27 width=17) (actual time=3.987..16.556 rows=1,002 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 4433
  • Buffers: shared hit=176
10. 5.735 11.137 ↑ 1.0 1,463 1

Hash (cost=35.63..35.63 rows=1,463 width=80) (actual time=11.135..11.137 rows=1,463 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 148kB
  • Buffers: shared hit=21
11. 5.402 5.402 ↑ 1.0 1,463 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..35.63 rows=1,463 width=80) (actual time=0.022..5.402 rows=1,463 loops=1)

  • Buffers: shared hit=21
12. 0.005 0.101 ↓ 0.0 0 1

Hash (cost=3.64..3.64 rows=1 width=16) (actual time=0.099..0.101 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=3
13. 0.024 0.096 ↓ 0.0 0 1

Hash Join (cost=2.33..3.64 rows=1 width=16) (actual time=0.093..0.096 rows=0 loops=1)

  • Hash Cond: (pc_1.projectid = pj_2.id)
  • Buffers: shared hit=3
14. 0.012 0.012 ↑ 24.0 1 1

Seq Scan on projectclient pc_1 (cost=0.00..1.24 rows=24 width=16) (actual time=0.010..0.012 rows=1 loops=1)

  • Buffers: shared hit=1
15. 0.007 0.060 ↓ 0.0 0 1

Hash (cost=2.31..2.31 rows=1 width=4) (actual time=0.057..0.060 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
16. 0.053 0.053 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..2.31 rows=1 width=4) (actual time=0.050..0.053 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=2
17. 0.133 0.630 ↓ 24.0 24 1

Hash (cost=3.70..3.70 rows=1 width=52) (actual time=0.627..0.630 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
18. 0.174 0.497 ↓ 24.0 24 1

Hash Join (cost=2.33..3.70 rows=1 width=52) (actual time=0.239..0.497 rows=24 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=3
19. 0.115 0.115 ↑ 1.0 24 1

Seq Scan on projectclient pc (cost=0.00..1.30 rows=24 width=48) (actual time=0.013..0.115 rows=24 loops=1)

  • Buffers: shared hit=1
20. 0.097 0.208 ↓ 25.0 25 1

Hash (cost=2.31..2.31 rows=1 width=8) (actual time=0.206..0.208 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
21. 0.111 0.111 ↓ 25.0 25 1

Seq Scan on project pj_1 (cost=0.00..2.31 rows=1 width=8) (actual time=0.008..0.111 rows=25 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=2
22. 1.166 2.240 ↓ 1.1 340 1

Hash (cost=7.19..7.19 rows=319 width=20) (actual time=2.237..2.240 rows=340 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
  • Buffers: shared hit=4
23. 1.074 1.074 ↓ 1.1 340 1

Seq Scan on timesheet ts (cost=0.00..7.19 rows=319 width=20) (actual time=0.013..1.074 rows=340 loops=1)

  • Buffers: shared hit=4
24. 0.109 0.248 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=5) (actual time=0.245..0.248 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
25. 0.139 0.139 ↑ 1.0 30 1

Seq Scan on userinfo ui_1 (cost=0.00..1.30 rows=30 width=5) (actual time=0.009..0.139 rows=30 loops=1)

  • Buffers: shared hit=1
26. 8.931 169.887 ↓ 6.1 1,296 1

Hash Left Join (cost=9.94..45.08 rows=214 width=1,897) (actual time=110.818..169.887 rows=1,296 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.projectid8 = pj.id)
  • Buffers: shared hit=215
27. 8.706 160.783 ↓ 6.1 1,296 1

Hash Left Join (cost=7.38..41.94 rows=214 width=1,381) (actual time=110.614..160.783 rows=1,296 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.clientid7 = cl.id)
  • Buffers: shared hit=213
28. 8.892 152.034 ↓ 6.1 1,296 1

Hash Join (cost=6.29..40.28 rows=214 width=865) (actual time=110.505..152.034 rows=1,296 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = login.userid)
  • Buffers: shared hit=212
29. 8.774 142.741 ↓ 6.1 1,296 1

Hash Left Join (cost=4.61..38.03 rows=214 width=353) (actual time=110.074..142.741 rows=1,296 loops=1)

  • Hash Cond: (ui.id = userhierarchy9.userid)
  • Buffers: shared hit=211
30. 8.956 133.207 ↓ 6.1 1,296 1

Hash Join (cost=1.68..34.08 rows=214 width=321) (actual time=109.295..133.207 rows=1,296 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = ui.id)
  • Buffers: shared hit=209
31. 123.993 123.993 ↑ 1.1 1,296 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts3 (cost=0.00..28.58 rows=1,429 width=81) (actual time=109.004..123.993 rows=1,296 loops=1)

  • Buffers: shared hit=208
32. 0.112 0.258 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=240) (actual time=0.255..0.258 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
33. 0.146 0.146 ↑ 1.0 30 1

Seq Scan on userinfo ui (cost=0.00..1.30 rows=30 width=240) (actual time=0.013..0.146 rows=30 loops=1)

  • Buffers: shared hit=1
34. 0.117 0.760 ↓ 9.3 28 1

Hash (cost=2.90..2.90 rows=3 width=36) (actual time=0.757..0.760 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
35. 0.267 0.643 ↓ 9.3 28 1

Hash Right Join (cost=1.46..2.90 rows=3 width=36) (actual time=0.265..0.643 rows=28 loops=1)

  • Hash Cond: (userinfo8.id = userhierarchy9.supervisorid)
  • Buffers: shared hit=2
36. 0.154 0.154 ↑ 1.0 30 1

Seq Scan on userinfo userinfo8 (cost=0.00..1.30 rows=30 width=36) (actual time=0.017..0.154 rows=30 loops=1)

  • Buffers: shared hit=1
37. 0.110 0.222 ↓ 9.3 28 1

Hash (cost=1.42..1.42 rows=3 width=8) (actual time=0.220..0.222 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
38. 0.112 0.112 ↓ 9.3 28 1

Seq Scan on userhierarchy userhierarchy9 (cost=0.00..1.42 rows=3 width=8) (actual time=0.011..0.112 rows=28 loops=1)

  • Filter: (('2019-07-05'::date >= startdate) AND ('2019-07-05'::date <= enddate))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
39. 0.203 0.401 ↑ 1.0 30 1

Hash (cost=1.30..1.30 rows=30 width=520) (actual time=0.398..0.401 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
40. 0.198 0.198 ↑ 1.0 30 1

Seq Scan on login (cost=0.00..1.30 rows=30 width=520) (actual time=0.021..0.198 rows=30 loops=1)

  • Buffers: shared hit=1
41. 0.020 0.043 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=520) (actual time=0.040..0.043 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
42. 0.023 0.023 ↑ 1.0 4 1

Seq Scan on clients cl (cost=0.00..1.04 rows=4 width=520) (actual time=0.008..0.023 rows=4 loops=1)

  • Buffers: shared hit=1
43. 0.088 0.173 ↑ 1.0 25 1

Hash (cost=2.25..2.25 rows=25 width=520) (actual time=0.170..0.173 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=2
44. 0.085 0.085 ↑ 1.0 25 1

Seq Scan on project pj (cost=0.00..2.25 rows=25 width=520) (actual time=0.009..0.085 rows=25 loops=1)

  • Buffers: shared hit=2
Planning time : 7.302 ms
Execution time : 187.975 ms