explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0co1

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

Sort (cost=51,042.02..51,083.52 rows=16,599 width=1,897) (actual time=5,242.549..5,275.712 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. 125.641 3,808.643 ↑ 1.0 16,208 1

HashAggregate (cost=38,426.51..38,592.50 rows=16,599 width=81) (actual time=3,755.935..3,808.643 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. 77.719 3,683.002 ↑ 1.0 16,566 1

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

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

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

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

Nested Loop Left Join (cost=13,470.78..37,209.48 rows=16,599 width=616) (actual time=2,145.216..3,424.216 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. 91.956 2,906.408 ↑ 1.0 16,566 1

Hash Left Join (cost=13,470.36..21,234.50 rows=16,599 width=105) (actual time=1,986.298..2,906.408 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. 134.751 939.723 ↑ 1.0 16,566 1

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

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=4664
9. 695.690 695.690 ↓ 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.046..695.690 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. 49.197 109.282 ↑ 1.0 16,566 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1745kB
  • Buffers: shared hit=517
11. 60.085 60.085 ↑ 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.034..60.085 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.675 1,874.729 ↑ 1.0 109,665 1

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

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

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

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

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

  • Buffers: shared hit=914
15. 280.389 663.329 ↑ 1.0 109,846 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 5315kB
  • Buffers: shared hit=5547
16. 382.940 382.940 ↑ 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.020..382.940 rows=109,846 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=5547
17. 39.970 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.006 158.822 ↓ 0.0 0 1

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

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

Seq Scan on project pj_2 (cost=0.00..6,920.07 rows=1 width=4) (actual time=158.814..158.816 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. 20.614 42.170 ↑ 1.0 4,028 1

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

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

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

  • Buffers: shared hit=47
23. 25.035 57.496 ↑ 1.0 10,174 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (dmv_timesheetday_facts3.clientid7 = cl.id)
  • Buffers: shared hit=17363
28. 3,904.313 3,904.313 ↑ 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,755.943..3,904.313 rows=16,208 loops=1)

  • Buffers: shared hit=16942
29. 88.714 180.356 ↑ 1.0 32,884 1

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

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

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

  • Buffers: shared hit=421
31. 301.482 571.946 ↑ 1.0 109,846 1

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

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

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

  • Buffers: shared hit=5547
33. 27.197 201.609 ↑ 1.0 10,174 1

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

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

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

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

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

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

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

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

Seq Scan on userhierarchy userhierarchy9 (cost=0.00..31.75 rows=161 width=8) (actual time=0.008..0.010 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. 26.703 52.539 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
42. 26.042 50.912 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=140
Planning time : 9.775 ms
Execution time : 5,308.329 ms