explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jqlm

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 6,699,911.005 ↓ 16.0 16 1

Sort (cost=2,515,677.44..2,515,677.45 rows=1 width=1,617) (actual time=6,699,911.005..6,699,911.005 rows=16 loops=1)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts9.timesheetstartdate3, dmv_timesheetday_facts9.timesheetenddate4, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts9.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((activities12.name)::character varying(50)) COLLATE "en_US", ((br.name)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=44,446,309
2.          

CTE dmv_timesheetday_facts0cte

3. 0.018 6,699,910.287 ↓ 16.0 16 1

GroupAggregate (cost=2,515,661.79..2,515,661.82 rows=1 width=69) (actual time=6,699,910.280..6,699,910.287 rows=16 loops=1)

  • Group Key: ts.id, ts_1.startdate, ts_1.enddate, ui_1.duplicatename, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.activityid, at.id
  • Buffers: shared hit=44,445,790
4. 0.045 6,699,910.269 ↓ 16.0 16 1

Sort (cost=2,515,661.79..2,515,661.79 rows=1 width=69) (actual time=6,699,910.268..6,699,910.269 rows=16 loops=1)

  • Sort Key: ts.id, ts_1.startdate, ts_1.enddate, ui_1.duplicatename, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END), at.activityid, at.id
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=44,445,790
5. 0.020 6,699,910.224 ↓ 16.0 16 1

Nested Loop (cost=2,400.18..2,515,661.78 rows=1 width=69) (actual time=3,850,760.380..6,699,910.224 rows=16 loops=1)

  • Buffers: shared hit=44,445,784
6. 0.036 6,699,910.140 ↓ 16.0 16 1

Nested Loop (cost=2,399.90..2,515,661.47 rows=1 width=77) (actual time=3,850,760.365..6,699,910.140 rows=16 loops=1)

  • Join Filter: (ts.userid = ui_1.id)
  • Buffers: shared hit=44,445,736
7. 0.025 6,699,910.056 ↓ 16.0 16 1

Nested Loop (cost=2,399.62..2,515,660.99 rows=1 width=80) (actual time=3,850,760.351..6,699,910.056 rows=16 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=44,445,688
8. 0.032 6,699,909.951 ↓ 16.0 16 1

Nested Loop (cost=2,399.19..2,515,660.53 rows=1 width=112) (actual time=3,850,760.321..6,699,909.951 rows=16 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=44,445,639
9. 63.341 6,699,909.823 ↓ 16.0 16 1

Nested Loop (cost=2,398.77..2,515,660.08 rows=1 width=88) (actual time=3,850,760.295..6,699,909.823 rows=16 loops=1)

  • Join Filter: (ts.id = at.timesheetid)
  • Rows Removed by Join Filter: 354,422
  • Buffers: shared hit=44,445,575
10. 2.265 18.648 ↓ 406.0 406 1

Nested Loop Semi Join (cost=0.70..7,604.79 rows=1 width=24) (actual time=0.088..18.648 rows=406 loops=1)

  • Buffers: shared hit=6,734
11. 11.256 11.256 ↓ 284.8 1,709 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..7,596.94 rows=6 width=20) (actual time=0.011..11.256 rows=1,709 loops=1)

  • Index Cond: ((startdate >= '2020-07-20'::date) AND (startdate <= '2020-07-26'::date))
  • Filter: ((enddate >= '2020-07-20'::date) AND (enddate <= '2020-07-26'::date))
  • Buffers: shared hit=3,300
12. 5.127 5.127 ↓ 0.0 0 1,709

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation4 (cost=0.28..1.31 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,709)

  • Index Cond: ((userid = ts.userid) AND (locationid = '01efd2df-bb0f-4e53-bf5e-3dffc572f0d7'::uuid) AND (startdate <= '2020-07-27'::date) AND (enddate >= '2020-07-27'::date))
  • Heap Fetches: 0
  • Buffers: shared hit=3,434
13. 182,896.066 6,699,827.834 ↑ 2.4 873 406

Hash Join (cost=2,398.07..2,508,029.50 rows=2,063 width=64) (actual time=87.320..16,502.039 rows=873 loops=406)

  • Hash Cond: (at.projectid = project5.id)
  • Buffers: shared hit=44,438,841
14. 5,761,294.884 6,516,931.736 ↓ 1.0 4,241,257 406

Hash Left Join (cost=2,389.73..2,454,467.65 rows=4,241,256 width=616) (actual time=0.049..16,051.556 rows=4,241,257 loops=406)

  • Hash Cond: (at.projectid = pj.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=44,438,835
15. 312,503.128 755,623.652 ↓ 1.0 4,241,257 406

Hash Left Join (cost=949.28..184,622.27 rows=4,241,256 width=85) (actual time=0.012..1,861.142 rows=4,241,257 loops=406)

  • Hash Cond: ((at.projectid = pj_1.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=44,437,955
16. 443,117.332 443,117.332 ↓ 1.0 4,241,257 406

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..151,863.56 rows=4,241,256 width=85) (actual time=0.003..1,091.422 rows=4,241,257 loops=406)

  • Buffers: shared hit=44,437,106
17. 0.010 3.192 ↓ 1.0 38 1

Hash (cost=948.73..948.73 rows=37 width=16) (actual time=3.191..3.192 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=849
18. 0.014 3.182 ↓ 1.0 38 1

Nested Loop (cost=0.29..948.73 rows=37 width=16) (actual time=0.268..3.182 rows=38 loops=1)

  • Buffers: shared hit=849
19. 3.092 3.092 ↑ 1.0 38 1

Seq Scan on project pj_1 (cost=0.00..909.85 rows=38 width=4) (actual time=0.257..3.092 rows=38 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 10,990
  • Buffers: shared hit=772
20. 0.076 0.076 ↑ 1.0 1 38

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.29..1.01 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=38)

  • Index Cond: (projectid = pj_1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=77
21. 1.974 13.200 ↑ 1.0 10,810 1

Hash (cost=1,305.31..1,305.31 rows=10,811 width=52) (actual time=13.200..13.200 rows=10,810 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 730kB
  • Buffers: shared hit=866
22. 2.410 11.226 ↑ 1.0 10,810 1

Hash Join (cost=1,047.22..1,305.31 rows=10,811 width=52) (actual time=5.277..11.226 rows=10,810 loops=1)

  • Hash Cond: (pc.projectid = pj.id)
  • Buffers: shared hit=866
23. 3.610 3.610 ↑ 1.0 10,848 1

Seq Scan on projectclient pc (cost=0.00..229.60 rows=10,848 width=48) (actual time=0.009..3.610 rows=10,848 loops=1)

  • Buffers: shared hit=94
24. 1.521 5.206 ↑ 1.0 10,990 1

Hash (cost=909.85..909.85 rows=10,990 width=8) (actual time=5.206..5.206 rows=10,990 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 558kB
  • Buffers: shared hit=772
25. 3.685 3.685 ↑ 1.0 10,990 1

Seq Scan on project pj (cost=0.00..909.85 rows=10,990 width=8) (actual time=0.003..3.685 rows=10,990 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=772
26. 0.003 0.032 ↑ 3.0 1 1

Hash (cost=8.30..8.30 rows=3 width=4) (actual time=0.032..0.032 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6
27. 0.000 0.029 ↑ 3.0 1 1

Unique (cost=8.25..8.27 rows=3 width=4) (actual time=0.029..0.029 rows=1 loops=1)

  • Buffers: shared hit=6
28. 0.007 0.029 ↑ 3.0 1 1

Sort (cost=8.25..8.26 rows=3 width=4) (actual time=0.028..0.029 rows=1 loops=1)

  • Sort Key: project5.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6
29. 0.000 0.022 ↑ 3.0 1 1

Nested Loop (cost=0.57..8.23 rows=3 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Buffers: shared hit=6
30. 0.007 0.007 ↑ 3.0 1 1

Index Scan using ixpcclientid on projectclient projectclient6 (cost=0.29..4.32 rows=3 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (clientid = 3,498)
  • Buffers: shared hit=3
31. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using project_pkey on project project5 (cost=0.29..1.30 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (id = projectclient6.projectid)
  • Heap Fetches: 0
  • Buffers: shared hit=3
32. 0.096 0.096 ↑ 1.0 1 16

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.44 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: (id = at.timesheetid)
  • Buffers: shared hit=64
33. 0.080 0.080 ↑ 1.0 1 16

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=16)

  • Index Cond: (timesheetid = at.timesheetid)
  • Heap Fetches: 0
  • Buffers: shared hit=49
34. 0.048 0.048 ↑ 1.0 1 16

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..0.47 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: (id = userlocation4.userid)
  • Buffers: shared hit=48
35. 0.064 0.064 ↑ 1.0 1 16

Index Scan using project_pkey on project project7 (cost=0.29..0.31 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=16)

  • Index Cond: (id = at.projectid)
  • Filter: (projectstatuslabelid = '3bbf6214-ea06-4044-8d23-558674d217f1'::uuid)
  • Buffers: shared hit=48
36.          

Initplan (for Sort)

37. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1)

  • Buffers: shared hit=1
38. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=1
39. 0.019 6,699,910.945 ↓ 16.0 16 1

Nested Loop (cost=3.29..13.59 rows=1 width=1,617) (actual time=6,699,910.489..6,699,910.945 rows=16 loops=1)

  • Buffers: shared hit=44,446,298
40. 0.013 6,699,910.878 ↓ 16.0 16 1

Nested Loop Left Join (cost=2.87..11.15 rows=1 width=151) (actual time=6,699,910.472..6,699,910.878 rows=16 loops=1)

  • Buffers: shared hit=44,446,234
41. 0.013 6,699,910.465 ↓ 16.0 16 1

Nested Loop (cost=0.87..6.39 rows=1 width=140) (actual time=6,699,910.360..6,699,910.465 rows=16 loops=1)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = login.userid)
  • Buffers: shared hit=44,445,935
42. 0.020 6,699,910.420 ↓ 16.0 16 1

Nested Loop (cost=0.59..6.06 rows=1 width=126) (actual time=6,699,910.348..6,699,910.420 rows=16 loops=1)

  • Buffers: shared hit=44,445,887
43. 0.010 6,699,910.368 ↓ 16.0 16 1

Nested Loop Left Join (cost=0.31..3.76 rows=1 width=92) (actual time=6,699,910.333..6,699,910.368 rows=16 loops=1)

  • Buffers: shared hit=44,445,839
44. 0.017 6,699,910.326 ↓ 16.0 16 1

Hash Right Join (cost=0.03..1.46 rows=1 width=73) (actual time=6,699,910.320..6,699,910.326 rows=16 loops=1)

  • Hash Cond: (activities12.id = dmv_timesheetday_facts9.activityid7)
  • Buffers: shared hit=44,445,791
45. 0.006 0.006 ↑ 1.0 30 1

Seq Scan on activities activities12 (cost=0.00..1.30 rows=30 width=12) (actual time=0.004..0.006 rows=30 loops=1)

  • Buffers: shared hit=1
46. 0.006 6,699,910.303 ↓ 16.0 16 1

Hash (cost=0.02..0.02 rows=1 width=69) (actual time=6,699,910.303..6,699,910.303 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=44,445,790
47. 6,699,910.297 6,699,910.297 ↓ 16.0 16 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts9 (cost=0.00..0.02 rows=1 width=69) (actual time=6,699,910.283..6,699,910.297 rows=16 loops=1)

  • Buffers: shared hit=44,445,790
48. 0.032 0.032 ↑ 1.0 1 16

Index Scan using clients_pkey on clients cl (cost=0.28..2.29 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=16)

  • Index Cond: (dmv_timesheetday_facts9.clientid6 = id)
  • Buffers: shared hit=48
49. 0.032 0.032 ↑ 1.0 1 16

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.30 rows=1 width=34) (actual time=0.002..0.002 rows=1 loops=16)

  • Index Cond: (id = dmv_timesheetday_facts9.userid2)
  • Buffers: shared hit=48
50. 0.032 0.032 ↑ 1.0 1 16

Index Scan using login_pkey on login (cost=0.28..0.33 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=16)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=48
51. 0.016 0.400 ↑ 13.0 1 16

Nested Loop (cost=1.99..4.62 rows=13 width=43) (actual time=0.024..0.025 rows=1 loops=16)

  • Buffers: shared hit=299
52. 0.016 0.288 ↑ 1.0 1 16

Nested Loop (cost=1.57..3.70 rows=1 width=51) (actual time=0.017..0.018 rows=1 loops=16)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
  • Buffers: shared hit=242
53. 0.016 0.224 ↑ 1.0 1 16

Nested Loop (cost=1.14..3.22 rows=1 width=67) (actual time=0.013..0.014 rows=1 loops=16)

  • Buffers: shared hit=178
54. 0.000 0.144 ↑ 1.0 1 16

Nested Loop (cost=0.71..2.75 rows=1 width=87) (actual time=0.009..0.009 rows=1 loops=16)

  • Buffers: shared hit=112
55. 0.096 0.096 ↑ 1.0 1 16

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..2.45 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: (dmv_timesheetday_facts9.timeallocationid8 = id)
  • Buffers: shared hit=64
56. 0.048 0.048 ↑ 1.0 1 16

Index Scan using billingrate_pkey on billingrate br (cost=0.28..0.30 rows=1 width=43) (actual time=0.002..0.003 rows=1 loops=16)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
  • Buffers: shared hit=48
57. 0.064 0.064 ↑ 1.0 1 16

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.43..0.46 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=16)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $8) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $9) AND (userid IS NULL)))
  • Buffers: shared hit=66
58. 0.048 0.048 ↑ 1.0 1 16

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.43..0.46 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: (projectbillingrateid = pbr.id)
  • Buffers: shared hit=64
59. 0.096 0.096 ↑ 9.0 1 16

Index Only Scan using ixtsuseridstartdateenddate on timesheet dmvts (cost=0.42..0.84 rows=9 width=12) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: ((userid = dm_attendancetimeallocation_facts.userid) AND (startdate <= dm_attendancetimeallocation_facts.entrydate) AND (enddate >= dm_attendancetimeallocation_facts.entrydate))
  • Heap Fetches: 0
  • Buffers: shared hit=57
60. 0.048 0.048 ↑ 1.0 1 16

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts dm_timesheetlist_facts14 (cost=0.42..2.44 rows=1 width=48) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: (timesheetid = dmv_timesheetday_facts9.timesheetid1)
  • Buffers: shared hit=64
Planning time : 28.405 ms
Execution time : 6,699,911.884 ms