explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UZkR

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

Sort (cost=74,138.27..74,179.77 rows=16,599 width=1,897) (actual time=5,537.777..5,576.256 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: external merge Disk: 3608kB
  • Buffers: shared hit=23273, temp read=2399 written=2386
2.          

CTE dmv_timesheetday_facts0cte

3. 101.898 4,010.285 ↑ 1.0 16,208 1

GroupAggregate (cost=44,536.53..45,117.49 rows=16,599 width=81) (actual time=3,874.472..4,010.285 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, temp read=985 written=977
4. 103.845 3,908.387 ↑ 1.0 16,566 1

Sort (cost=44,536.53..44,578.02 rows=16,599 width=81) (actual time=3,874.295..3,908.387 rows=16,566 loops=1)

  • Sort Key: ts.userid, at.entrydate, ui_1.duplicatename, (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.projectid
  • Sort Method: quicksort Memory: 2915kB
  • Buffers: shared hit=16942, temp read=985 written=977
5. 77.054 3,804.542 ↑ 1.0 16,566 1

Hash Join (cost=17,920.14..43,373.03 rows=16,599 width=81) (actual time=2,348.009..3,804.542 rows=16,566 loops=1)

  • Hash Cond: (ts.userid = ui_1.id)
  • Buffers: shared hit=16942, temp read=985 written=977
6. 91.794 3,669.108 ↑ 1.0 16,566 1

Hash Join (cost=17,468.22..42,877.53 rows=16,599 width=80) (actual time=2,289.578..3,669.108 rows=16,566 loops=1)

  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=16719, temp read=985 written=977
7. 327.847 3,556.366 ↑ 1.0 16,566 1

Nested Loop Left Join (cost=17,329.78..42,529.48 rows=16,599 width=616) (actual time=2,268.566..3,556.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, temp read=985 written=977
8. 124.904 3,029.727 ↑ 1.0 16,566 1

Hash Left Join (cost=17,329.36..26,554.50 rows=16,599 width=105) (actual time=2,104.530..3,029.727 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, temp read=985 written=977
9. 129.973 903.330 ↑ 1.0 16,566 1

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

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=4664
10. 672.595 672.595 ↓ 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.041..672.595 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
11. 45.717 100.762 ↑ 1.0 16,566 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1745kB
  • Buffers: shared hit=517
12. 55.045 55.045 ↑ 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.042..55.045 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
13. 316.071 2,001.493 ↑ 1.0 109,665 1

Hash (cost=13,653.84..13,653.84 rows=109,665 width=52) (actual time=2,001.491..2,001.493 rows=109,665 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2047kB
  • Buffers: shared hit=6461, temp read=465 written=914
14. 620.308 1,685.422 ↑ 1.0 109,665 1

Hash Join (cost=8,723.15..13,653.84 rows=109,665 width=52) (actual time=695.673..1,685.422 rows=109,665 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=6461, temp read=465 written=463
15. 369.578 369.578 ↑ 1.0 109,665 1

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

  • Buffers: shared hit=914
16. 292.373 695.536 ↑ 1.0 109,846 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3169kB
  • Buffers: shared hit=5547, temp written=187
17. 403.163 403.163 ↑ 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.011..403.163 rows=109,846 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=5547
18. 34.914 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
19. 0.049 163.878 ↓ 0.0 0 1

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

  • Buffers: shared hit=5547
20. 163.829 163.829 ↓ 0.0 0 1

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

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 109846
  • Buffers: shared hit=5547
21. 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
22. 10.671 20.948 ↑ 1.0 4,028 1

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

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

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

  • Buffers: shared hit=47
24. 25.550 58.380 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
26. 75.471 5,391.071 ↑ 1.0 16,208 1

Hash Left Join (cost=11,666.36..14,182.79 rows=16,599 width=1,897) (actual time=4,722.246..5,391.071 rows=16,208 loops=1)

  • Hash Cond: (ui.id = userhierarchy9.userid)
  • Buffers: shared hit=23273, temp read=1948 written=1934
27. 78.485 5,315.584 ↑ 1.0 16,208 1

Hash Left Join (cost=11,180.25..13,320.58 rows=16,599 width=208) (actual time=4,722.211..5,315.584 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.clientid7 = cl.id)
  • Buffers: shared hit=23273, temp read=1948 written=1934
28. 77.152 5,070.585 ↑ 1.0 16,208 1

Hash Join (cost=10,019.36..12,116.11 rows=16,599 width=182) (actual time=4,555.464..5,070.585 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = login.userid)
  • Buffers: shared hit=22852, temp read=1948 written=1934
29. 76.504 4,941.902 ↑ 1.0 16,208 1

Hash Join (cost=9,650.45..11,703.60 rows=16,599 width=155) (actual time=4,503.916..4,941.902 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = ui.id)
  • Buffers: shared hit=22712, temp read=1948 written=1934
30. 122.128 4,807.453 ↑ 1.0 16,208 1

Hash Left Join (cost=9,198.53..11,208.09 rows=16,599 width=136) (actual time=4,445.941..4,807.453 rows=16,208 loops=1)

  • Hash Cond: (dmv_timesheetday_facts3.projectid8 = pj.id)
  • Buffers: shared hit=22489, temp read=1948 written=1934
31. 4,114.325 4,114.325 ↑ 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,874.491..4,114.325 rows=16,208 loops=1)

  • Buffers: shared hit=16942, temp read=985 written=977
32. 306.961 571.000 ↑ 1.0 109,846 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3002kB
  • Buffers: shared hit=5547, temp written=811
33. 264.039 264.039 ↑ 1.0 109,846 1

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

  • Buffers: shared hit=5547
34. 33.093 57.945 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
36. 26.676 51.531 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=140
38. 88.342 166.514 ↑ 1.0 32,884 1

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

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

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

  • Buffers: shared hit=421
40. 0.004 0.016 ↓ 0.0 0 1

Hash (cost=484.09..484.09 rows=161 width=22) (actual time=0.014..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.004 0.012 ↓ 0.0 0 1

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

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

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

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

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

44. 0.000 0.000 ↓ 0.0 0

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

Planning time : 8.639 ms
Execution time : 5,611.322 ms