explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XWxC

Settings
# exclusive inclusive rows x rows loops node
1. 172.586 4,629.607 ↑ 1.0 16,208 1

Sort (cost=46,013.74..46,055.24 rows=16,599 width=1,897) (actual time=4,596.718..4,629.607 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=23235 read=320
2.          

CTE dmv_timesheetday_facts0cte

3. 121.580 3,165.161 ↑ 1.0 16,208 1

HashAggregate (cost=33,398.23..33,564.22 rows=16,599 width=81) (actual time=3,108.893..3,165.161 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=16904 read=320
4. 80.374 3,043.581 ↑ 1.0 16,566 1

Hash Join (cost=17,165.57..33,024.75 rows=16,599 width=81) (actual time=2,629.102..3,043.581 rows=16,566 loops=1)

  • Hash Cond: (ts.userid = ui_1.id)
  • Buffers: shared hit=16904 read=320
5. 82.755 2,901.541 ↑ 1.0 16,566 1

Hash Join (cost=16,713.65..32,529.24 rows=16,599 width=80) (actual time=2,567.402..2,901.541 rows=16,566 loops=1)

  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=16681 read=320
6. 251.610 2,798.326 ↑ 1.0 16,566 1

Hash Right Join (cost=16,575.21..32,181.19 rows=16,599 width=616) (actual time=2,546.894..2,798.326 rows=16,566 loops=1)

  • Hash Cond: ((pj_2.id = at.projectid) AND (pc_1.clientid = at.userspecifiedclientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=16634 read=320
7. 0.026 161.908 ↓ 0.0 0 1

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

  • Buffers: shared hit=5547
8. 161.882 161.882 ↓ 0.0 0 1

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

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 109846
  • Buffers: shared hit=5547
9. 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
10. 47.181 2,384.808 ↑ 1.0 16,566 1

Hash (cost=16,325.81..16,325.81 rows=16,599 width=105) (actual time=2,384.806..2,384.808 rows=16,566 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1835kB
  • Buffers: shared hit=11087 read=320
11. 85.027 2,337.627 ↑ 1.0 16,566 1

Hash Left Join (cost=13,519.32..16,325.81 rows=16,599 width=105) (actual time=2,043.793..2,337.627 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=11087 read=320
12. 124.650 320.690 ↑ 1.0 16,566 1

Hash Right Join (cost=1,282.66..3,778.33 rows=16,599 width=65) (actual time=111.867..320.690 rows=16,566 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=4626 read=320
13. 86.008 95.693 ↓ 30.8 33,948 1

Bitmap Heap Scan on dm_attendancetimeallocation_metadata isbillable (cost=48.95..2,541.73 rows=1,101 width=17) (actual time=10.936..95.693 rows=33,948 loops=1)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Heap Blocks: exact=4109
  • Buffers: shared hit=4109 read=320
14. 9.685 9.685 ↓ 30.8 33,948 1

Bitmap Index Scan on ix_temp (cost=0.00..48.68 rows=1,101 width=0) (actual time=9.683..9.685 rows=33,948 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Buffers: shared read=320
15. 45.209 100.347 ↑ 1.0 16,566 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1745kB
  • Buffers: shared hit=517
16. 55.138 55.138 ↑ 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.019..55.138 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
17. 317.910 1,931.910 ↑ 1.0 109,665 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 7129kB
  • Buffers: shared hit=6461
18. 557.297 1,614.000 ↑ 1.0 109,665 1

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

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=6461
19. 374.444 374.444 ↑ 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..374.444 rows=109,665 loops=1)

  • Buffers: shared hit=914
20. 295.107 682.259 ↑ 1.0 109,846 1

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

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

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=5547
22. 10.849 20.460 ↑ 1.0 4,028 1

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

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

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

  • Buffers: shared hit=47
24. 24.560 61.666 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
26. 83.100 4,457.021 ↑ 1.0 16,208 1

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

  • Hash Cond: (dmv_timesheetday_facts3.userid4 = ui.id)
  • Buffers: shared hit=23235 read=320
27. 85.875 4,163.960 ↑ 1.0 16,208 1

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

  • Hash Cond: (dmv_timesheetday_facts3.projectid8 = pj.id)
  • Buffers: shared hit=22872 read=320
28. 87.801 3,507.854 ↑ 1.0 16,208 1

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

  • Hash Cond: (dmv_timesheetday_facts3.clientid7 = cl.id)
  • Buffers: shared hit=17325 read=320
29. 3,253.062 3,253.062 ↑ 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,108.902..3,253.062 rows=16,208 loops=1)

  • Buffers: shared hit=16904 read=320
30. 88.307 166.991 ↑ 1.0 32,884 1

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

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

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

  • Buffers: shared hit=421
32. 287.871 570.231 ↑ 1.0 109,846 1

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

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

  • Buffers: shared hit=5547
34. 27.213 209.961 ↑ 1.0 10,174 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 991kB
  • Buffers: shared hit=363
35. 48.662 182.748 ↑ 1.0 10,174 1

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

  • Hash Cond: (ui.id = login.userid)
  • Buffers: shared hit=363
36. 24.355 77.481 ↑ 1.0 10,174 1

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

  • Hash Cond: (userhierarchy9.userid = ui.id)
  • Buffers: shared hit=223
37. 0.005 0.015 ↓ 0.0 0 1

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

  • Hash Cond: (userhierarchy9.supervisorid = userinfo8.id)
38. 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.009..0.010 rows=0 loops=1)

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

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

40. 0.000 0.000 ↓ 0.0 0

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

41. 27.047 53.111 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
43. 28.705 56.605 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=140
Planning time : 11.738 ms
Execution time : 4,662.909 ms