explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eYda

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

Sort (cost=46,029.74..46,071.24 rows=16,599 width=1,897) (actual time=4,593.227..4,625.035 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=393
2.          

CTE dmv_timesheetday_facts0cte

3. 137.940 3,117.006 ↑ 1.0 16,208 1

HashAggregate (cost=33,414.23..33,580.22 rows=16,599 width=81) (actual time=3,057.160..3,117.006 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=393
4. 75.271 2,979.066 ↑ 1.0 16,566 1

Hash Join (cost=17,181.57..33,040.75 rows=16,599 width=81) (actual time=2,564.987..2,979.066 rows=16,566 loops=1)

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

Hash Join (cost=16,729.65..32,545.24 rows=16,599 width=80) (actual time=2,508.436..2,847.274 rows=16,566 loops=1)

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

Hash Right Join (cost=16,591.21..32,197.19 rows=16,599 width=616) (actual time=2,488.947..2,746.483 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=393
7. 0.006 160.446 ↓ 0.0 0 1

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

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

Seq Scan on project pj_2 (cost=0.00..6,920.07 rows=1 width=4) (actual time=160.439..160.440 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.245 2,328.420 ↑ 1.0 16,566 1

Hash (cost=16,341.81..16,341.81 rows=16,599 width=105) (actual time=2,328.418..2,328.420 rows=16,566 loops=1)

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

Hash Left Join (cost=13,535.32..16,341.81 rows=16,599 width=105) (actual time=1,986.385..2,281.175 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=393
12. 122.835 324.162 ↑ 1.0 16,566 1

Hash Right Join (cost=1,298.66..3,794.33 rows=16,599 width=65) (actual time=113.707..324.162 rows=16,566 loops=1)

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

Bitmap Heap Scan on dm_attendancetimeallocation_metadata isbillable (cost=64.95..2,557.73 rows=1,101 width=17) (actual time=12.794..101.002 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=393
14. 11.505 11.505 ↓ 30.8 33,948 1

Bitmap Index Scan on ix_temp (cost=0.00..64.68 rows=1,101 width=0) (actual time=11.503..11.505 rows=33,948 loops=1)

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1745kB
  • Buffers: shared hit=517
16. 54.951 54.951 ↑ 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..54.951 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. 306.844 1,872.251 ↑ 1.0 109,665 1

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

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

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

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

  • Buffers: shared hit=914
20. 281.627 670.259 ↑ 1.0 109,846 1

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

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

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

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

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

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

  • Buffers: shared hit=47
24. 25.204 56.521 ↑ 1.0 10,174 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 526kB
  • Buffers: shared hit=223
25. 31.317 31.317 ↑ 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..31.317 rows=10,174 loops=1)

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

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

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

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

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

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

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

  • Buffers: shared hit=16904 read=393
30. 85.975 170.383 ↑ 1.0 32,884 1

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

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

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

  • Buffers: shared hit=421
32. 317.381 601.974 ↑ 1.0 109,846 1

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

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

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

  • Buffers: shared hit=5547
34. 28.593 203.408 ↑ 1.0 10,174 1

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

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

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

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

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

  • Hash Cond: (userhierarchy9.userid = ui.id)
  • Buffers: shared hit=223
37. 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)
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.008..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. 25.428 49.754 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=223
43. 28.011 52.151 ↑ 1.0 10,174 1

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

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

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

  • Buffers: shared hit=140
Planning time : 10.291 ms
Execution time : 4,658.936 ms