explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IyV

Settings
# exclusive inclusive rows x rows loops node
1. 174.807 5,275.344 ↑ 1.0 16,208 1

Sort (cost=51,042.02..51,083.52 rows=16,599 width=1,897) (actual time=5,243.979..5,275.344 rows=16,208 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: 5259kB
  • Buffers: shared hit=23273
2.          

CTE dmv_timesheetday_facts0cte

3. 132.421 3,744.841 ↑ 1.0 16,208 1

HashAggregate (cost=38,426.51..38,592.50 rows=16,599 width=81) (actual time=3,689.356..3,744.841 rows=16,208 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=16942
4. 78.592 3,612.420 ↑ 1.0 16,566 1

Hash Join (cost=14,061.14..38,053.03 rows=16,599 width=81) (actual time=2,197.745..3,612.420 rows=16,566 loops=1)

  • Hash Cond: (ts.userid = ui_1.id)
  • Buffers: shared hit=16942
5. 81.593 3,478.694 ↑ 1.0 16,566 1

Hash Join (cost=13,609.22..37,557.53 rows=16,599 width=80) (actual time=2,142.551..3,478.694 rows=16,566 loops=1)

  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=16719
6. 328.704 3,378.366 ↑ 1.0 16,566 1

Nested Loop Left Join (cost=13,470.78..37,209.48 rows=16,599 width=616) (actual time=2,123.755..3,378.366 rows=16,566 loops=1)

  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate) AND (pj_2.id = at.projectid) AND (pc_1.clientid = at.userspecifiedclientid))
  • Buffers: shared hit=16672
7. 88.802 2,850.870 ↑ 1.0 16,566 1

Hash Left Join (cost=13,470.36..21,234.50 rows=16,599 width=105) (actual time=1,964.359..2,850.870 rows=16,566 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=11125
8. 131.177 898.242 ↑ 1.0 16,566 1

Hash Right Join (cost=1,233.71..8,687.02 rows=16,599 width=65) (actual time=100.126..898.242 rows=16,566 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=4664
9. 669.182 669.182 ↓ 30.8 33,948 1

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..7,450.42 rows=1,101 width=17) (actual time=0.039..669.182 rows=33,948 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 186280
  • Buffers: shared hit=4147
10. 43.519 97.883 ↑ 1.0 16,566 1

Hash (cost=1,026.22..1,026.22 rows=16,599 width=80) (actual time=97.881..97.883 rows=16,566 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1745kB
  • Buffers: shared hit=517
11. 54.364 54.364 ↑ 1.0 16,566 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..1,026.22 rows=16,599 width=80) (actual time=0.020..54.364 rows=16,566 loops=1)

  • Filter: ((entrydate >= '2019-01-01'::date) AND (entrydate <= '2019-03-31'::date))
  • Rows Removed by Filter: 17382
  • Buffers: shared hit=517
12. 307.252 1,863.826 ↑ 1.0 109,665 1

Hash (cost=10,865.84..10,865.84 rows=109,665 width=52) (actual time=1,863.824..1,863.826 rows=109,665 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7129kB
  • Buffers: shared hit=6461
13. 528.449 1,556.574 ↑ 1.0 109,665 1

Hash Join (cost=8,293.15..10,865.84 rows=109,665 width=52) (actual time=681.957..1,556.574 rows=109,665 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=6461
14. 346.638 346.638 ↑ 1.0 109,665 1

Seq Scan on projectclient pc (cost=0.00..2,284.81 rows=109,665 width=48) (actual time=0.019..346.638 rows=109,665 loops=1)

  • Buffers: shared hit=914
15. 281.781 681.487 ↑ 1.0 109,846 1

Hash (cost=6,920.07..6,920.07 rows=109,846 width=8) (actual time=681.485..681.487 rows=109,846 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5315kB
  • Buffers: shared hit=5547
16. 399.706 399.706 ↑ 1.0 109,846 1

Seq Scan on project pj_1 (cost=0.00..6,920.07 rows=109,846 width=8) (actual time=0.026..399.706 rows=109,846 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=5547
17. 39.552 198.792 ↓ 0.0 0 16,566

Materialize (cost=0.42..6,928.52 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=16,566)

  • Buffers: shared hit=5547
18. 0.053 159.240 ↓ 0.0 0 1

Nested Loop (cost=0.42..6,928.52 rows=1 width=16) (actual time=159.238..159.240 rows=0 loops=1)

  • Buffers: shared hit=5547
19. 159.187 159.187 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..6,920.07 rows=1 width=4) (actual time=159.185..159.187 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 109846
  • Buffers: shared hit=5547
20. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
21. 9.725 18.735 ↑ 1.0 4,028 1

Hash (cost=87.64..87.64 rows=4,064 width=20) (actual time=18.733..18.735 rows=4,028 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 237kB
  • Buffers: shared hit=47
22. 9.010 9.010 ↑ 1.0 4,028 1

Seq Scan on timesheet ts (cost=0.00..87.64 rows=4,064 width=20) (actual time=0.017..9.010 rows=4,028 loops=1)

  • Buffers: shared hit=47
23. 23.839 55.134 ↑ 1.0 10,174 1

Hash (cost=324.74..324.74 rows=10,174 width=5) (actual time=55.132..55.134 rows=10,174 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 526kB
  • Buffers: shared hit=223
24. 31.295 31.295 ↑ 1.0 10,174 1

Seq Scan on userinfo ui_1 (cost=0.00..324.74 rows=10,174 width=5) (actual time=0.026..31.295 rows=10,174 loops=1)

  • Buffers: shared hit=223
25. 97.683 5,100.537 ↑ 1.0 16,208 1

Hash Join (cost=10,638.66..11,286.03 rows=16,599 width=1,897) (actual time=4,686.545..5,100.537 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = ui.id)
  • Buffers: shared hit=23273
26. 80.026 4,761.126 ↑ 1.0 16,208 1

Hash Left Join (cost=9,179.43..9,598.56 rows=16,599 width=162) (actual time=4,444.764..4,761.126 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.projectid8 = pj.id)
  • Buffers: shared hit=22910
27. 80.717 4,090.150 ↑ 1.0 16,208 1

Hash Left Join (cost=1,160.89..1,536.45 rows=16,599 width=107) (actual time=3,853.333..4,090.150 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.clientid7 = cl.id)
  • Buffers: shared hit=17363
28. 3,845.713 3,845.713 ↑ 1.0 16,208 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts3 (cost=0.00..331.98 rows=16,599 width=81) (actual time=3,689.365..3,845.713 rows=16,208 loops=1)

  • Buffers: shared hit=16942
29. 85.563 163.720 ↑ 1.0 32,884 1

Hash (cost=749.84..749.84 rows=32,884 width=30) (actual time=163.718..163.720 rows=32,884 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2553kB
  • Buffers: shared hit=421
30. 78.157 78.157 ↑ 1.0 32,884 1

Seq Scan on clients cl (cost=0.00..749.84 rows=32,884 width=30) (actual time=0.015..78.157 rows=32,884 loops=1)

  • Buffers: shared hit=421
31. 309.275 590.950 ↑ 1.0 109,846 1

Hash (cost=6,645.46..6,645.46 rows=109,846 width=59) (actual time=590.949..590.950 rows=109,846 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 10984kB
  • Buffers: shared hit=5547
32. 281.675 281.675 ↑ 1.0 109,846 1

Seq Scan on project pj (cost=0.00..6,645.46 rows=109,846 width=59) (actual time=0.012..281.675 rows=109,846 loops=1)

  • Buffers: shared hit=5547
33. 37.614 241.728 ↑ 1.0 10,174 1

Hash (cost=1,332.06..1,332.06 rows=10,174 width=68) (actual time=241.726..241.728 rows=10,174 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 991kB
  • Buffers: shared hit=363
34. 67.369 204.114 ↑ 1.0 10,174 1

Hash Join (cost=1,272.74..1,332.06 rows=10,174 width=68) (actual time=104.589..204.114 rows=10,174 loops=1)

  • Hash Cond: (ui.id = login.userid)
  • Buffers: shared hit=363
35. 32.235 87.624 ↑ 1.0 10,174 1

Hash Right Join (cost=903.83..936.43 rows=10,174 width=37) (actual time=55.418..87.624 rows=10,174 loops=1)

  • Hash Cond: (userhierarchy9.userid = ui.id)
  • Buffers: shared hit=223
36. 0.009 0.024 ↓ 0.0 0 1

Hash Left Join (cost=451.92..484.09 rows=161 width=22) (actual time=0.023..0.024 rows=0 loops=1)

  • Hash Cond: (userhierarchy9.supervisorid = userinfo8.id)
37. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on userhierarchy userhierarchy9 (cost=0.00..31.75 rows=161 width=8) (actual time=0.013..0.015 rows=0 loops=1)

  • Filter: (('2019-06-08'::date >= startdate) AND ('2019-06-08'::date <= enddate))
38. 0.000 0.000 ↓ 0.0 0

Hash (cost=324.74..324.74 rows=10,174 width=22) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on userinfo userinfo8 (cost=0.00..324.74 rows=10,174 width=22) (never executed)

40. 27.786 55.365 ↑ 1.0 10,174 1

Hash (cost=324.74..324.74 rows=10,174 width=19) (actual time=55.363..55.365 rows=10,174 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 663kB
  • Buffers: shared hit=223
41. 27.579 27.579 ↑ 1.0 10,174 1

Seq Scan on userinfo ui (cost=0.00..324.74 rows=10,174 width=19) (actual time=0.013..27.579 rows=10,174 loops=1)

  • Buffers: shared hit=223
42. 24.823 49.121 ↑ 1.0 10,174 1

Hash (cost=241.74..241.74 rows=10,174 width=31) (actual time=49.120..49.121 rows=10,174 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 775kB
  • Buffers: shared hit=140
43. 24.298 24.298 ↑ 1.0 10,174 1

Seq Scan on login (cost=0.00..241.74 rows=10,174 width=31) (actual time=0.045..24.298 rows=10,174 loops=1)

  • Buffers: shared hit=140