explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sfUk : eXcell

Settings
# exclusive inclusive rows x rows loops node
1. 0.133 11,032,460.484 ↓ 17.0 17 1

Sort (cost=1,365,017.07..1,365,017.08 rows=1 width=2,385) (actual time=11,032,460.483..11,032,460.484 rows=17 loops=1)

  • Sort Key: ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts7.userduplicatename3, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts7.timesheetstartdate4, dmv_timesheetday_facts7.timesheetenddate5
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=56,299,884 read=13
2.          

CTE dmv_timesheetday_facts0cte

3. 0.018 11,032,460.102 ↓ 17.0 17 1

GroupAggregate (cost=1,365,011.60..1,365,011.63 rows=1 width=49) (actual time=11,032,460.092..11,032,460.102 rows=17 loops=1)

  • Group Key: ts.userid, at.id, ui_1.duplicatename, ts_1.startdate, ts_1.enddate, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END)
  • Buffers: shared hit=56,299,635 read=13
4. 0.036 11,032,460.084 ↓ 17.0 17 1

Sort (cost=1,365,011.60..1,365,011.60 rows=1 width=49) (actual time=11,032,460.083..11,032,460.084 rows=17 loops=1)

  • Sort Key: ts.userid, at.id, ui_1.duplicatename, ts_1.startdate, ts_1.enddate, (CASE WHEN (pj.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END)
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=56,299,635 read=13
5. 0.040 11,032,460.048 ↓ 17.0 17 1

Nested Loop (cost=1,509.55..1,365,011.59 rows=1 width=49) (actual time=506,172.256..11,032,460.048 rows=17 loops=1)

  • Buffers: shared hit=56,299,629 read=13
6. 0.036 11,032,459.974 ↓ 17.0 17 1

Nested Loop (cost=1,509.27..1,365,009.28 rows=1 width=48) (actual time=506,172.245..11,032,459.974 rows=17 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=56,299,578 read=13
7. 0.035 11,032,459.785 ↓ 17.0 17 1

Nested Loop (cost=1,508.85..1,365,008.83 rows=1 width=96) (actual time=506,172.192..11,032,459.785 rows=17 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=56,299,493 read=13
8. 7,173.946 11,032,459.665 ↓ 17.0 17 1

Nested Loop (cost=1,508.42..1,365,008.37 rows=1 width=72) (actual time=506,172.165..11,032,459.665 rows=17 loops=1)

  • Join Filter: (at.timesheetid = ts.id)
  • Rows Removed by Join Filter: 74,691,028
  • Buffers: shared hit=56,299,425 read=13
9. 30.308 30.308 ↓ 1,431.0 1,431 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..5,782.84 rows=1 width=20) (actual time=0.276..30.308 rows=1,431 loops=1)

  • Index Cond: ((startdate >= '2020-06-06'::date) AND (startdate <= '2020-06-21'::date))
  • Filter: ((enddate >= '2020-06-06'::date) AND (enddate <= '2020-06-21'::date))
  • Rows Removed by Filter: 706
  • Buffers: shared hit=2,733 read=9
10. 388,482.397 11,025,255.411 ↓ 2.1 52,195 1,431

Hash Join (cost=1,508.00..1,358,921.98 rows=24,284 width=52) (actual time=2.619..7,704.581 rows=52,195 loops=1,431)

  • Hash Cond: (at.projectid = project4.id)
  • Buffers: shared hit=56,296,692 read=4
11. 9,349,257.490 10,636,766.100 ↓ 1.0 2,330,475 1,431

Hash Left Join (cost=1,428.25..1,329,421.01 rows=2,330,036 width=616) (actual time=0.014..7,433.100 rows=2,330,475 loops=1,431)

  • Hash Cond: (at.projectid = pj.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Rows Removed by Join Filter: 447,925
  • Buffers: shared hit=56,296,557
12. 879,982.863 1,287,500.751 ↓ 1.0 2,330,474 1,431

Hash Left Join (cost=556.62..80,672.27 rows=2,330,036 width=81) (actual time=0.005..899.721 rows=2,330,474 loops=1,431)

  • 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=56,296,014
13. 407,515.887 407,515.887 ↓ 1.0 2,330,474 1,431

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..62,640.36 rows=2,330,036 width=81) (actual time=0.003..284.777 rows=2,330,474 loops=1,431)

  • Buffers: shared hit=56,295,540
14. 0.001 2.001 ↓ 0.0 0 1

Hash (cost=556.61..556.61 rows=1 width=16) (actual time=2.001..2.001 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=474
15. 0.000 2.000 ↓ 0.0 0 1

Nested Loop (cost=0.28..556.61 rows=1 width=16) (actual time=2.000..2.000 rows=0 loops=1)

  • Buffers: shared hit=474
16. 2.000 2.000 ↓ 0.0 0 1

Seq Scan on project pj_1 (cost=0.00..555.30 rows=1 width=4) (actual time=2.000..2.000 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 6,528
  • Buffers: shared hit=474
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.28..1.30 rows=1 width=16) (never executed)

  • Index Cond: (projectid = pj_1.id)
  • Heap Fetches: 0
18. 1.172 7.859 ↓ 1.0 6,550 1

Hash (cost=790.62..790.62 rows=6,480 width=52) (actual time=7.859..7.859 rows=6,550 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 429kB
  • Buffers: shared hit=530
19. 1.351 6.687 ↓ 1.0 6,550 1

Hash Join (cost=636.60..790.62 rows=6,480 width=52) (actual time=3.205..6.687 rows=6,550 loops=1)

  • Hash Cond: (pc.projectid = pj.id)
  • Buffers: shared hit=530
20. 2.168 2.168 ↓ 1.0 6,550 1

Seq Scan on projectclient pc (cost=0.00..137.00 rows=6,480 width=48) (actual time=0.008..2.168 rows=6,550 loops=1)

  • Buffers: shared hit=56
21. 0.875 3.168 ↓ 1.0 6,528 1

Hash (cost=555.30..555.30 rows=6,504 width=8) (actual time=3.168..3.168 rows=6,528 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 319kB
  • Buffers: shared hit=474
22. 2.293 2.293 ↓ 1.0 6,528 1

Seq Scan on project pj (cost=0.00..555.30 rows=6,504 width=8) (actual time=0.003..2.293 rows=6,528 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=474
23. 0.011 6.914 ↑ 1.0 60 1

Hash (cost=79.01..79.01 rows=60 width=4) (actual time=6.913..6.914 rows=60 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=135 read=4
24. 0.025 6.903 ↑ 1.0 60 1

HashAggregate (cost=77.81..78.41 rows=60 width=4) (actual time=6.896..6.903 rows=60 loops=1)

  • Group Key: project4.id
  • Buffers: shared hit=135 read=4
25. 0.009 6.878 ↑ 1.0 60 1

Nested Loop (cost=0.56..77.66 rows=60 width=4) (actual time=1.758..6.878 rows=60 loops=1)

  • Buffers: shared hit=135 read=4
26. 3.329 3.329 ↑ 1.0 60 1

Index Scan using ixpcclientid on projectclient projectclient5 (cost=0.28..22.66 rows=60 width=4) (actual time=1.697..3.329 rows=60 loops=1)

  • Index Cond: (clientid = 656)
  • Buffers: shared hit=10 read=2
27. 3.540 3.540 ↑ 1.0 1 60

Index Only Scan using project_pkey on project project4 (cost=0.28..0.92 rows=1 width=4) (actual time=0.059..0.059 rows=1 loops=60)

  • Index Cond: (id = projectclient5.projectid)
  • Heap Fetches: 6
  • Buffers: shared hit=125 read=2
28. 0.085 0.085 ↑ 1.0 1 17

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

  • Index Cond: (id = at.timesheetid)
  • Buffers: shared hit=68
29. 0.153 0.153 ↑ 1.0 1 17

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

  • Index Cond: (timesheetid = at.timesheetid)
  • Heap Fetches: 17
  • Buffers: shared hit=85
30. 0.034 0.034 ↑ 1.0 1 17

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.29..2.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = ts.userid)
  • Buffers: shared hit=51
31. 0.008 11,032,460.351 ↓ 17.0 17 1

Nested Loop (cost=1.27..5.43 rows=1 width=2,385) (actual time=11,032,460.176..11,032,460.351 rows=17 loops=1)

  • Buffers: shared hit=56,299,873 read=13
32. 0.015 11,032,460.309 ↓ 17.0 17 1

Nested Loop (cost=1.13..5.28 rows=1 width=117) (actual time=11,032,460.158..11,032,460.309 rows=17 loops=1)

  • Join Filter: (dmv_timesheetday_facts7.userid1 = du.userid)
  • Buffers: shared hit=56,299,839 read=13
33. 0.007 11,032,460.260 ↓ 17.0 17 1

Nested Loop Left Join (cost=0.84..4.95 rows=1 width=117) (actual time=11,032,460.143..11,032,460.260 rows=17 loops=1)

  • Buffers: shared hit=56,299,788 read=13
34. 0.010 11,032,460.219 ↓ 17.0 17 1

Nested Loop (cost=0.57..2.65 rows=1 width=99) (actual time=11,032,460.131..11,032,460.219 rows=17 loops=1)

  • Join Filter: (dmv_timesheetday_facts7.userid1 = login.userid)
  • Buffers: shared hit=56,299,737 read=13
35. 0.027 11,032,460.175 ↓ 17.0 17 1

Nested Loop (cost=0.29..2.32 rows=1 width=87) (actual time=11,032,460.119..11,032,460.175 rows=17 loops=1)

  • Buffers: shared hit=56,299,686 read=13
36. 11,032,460.114 11,032,460.114 ↓ 17.0 17 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts7 (cost=0.00..0.02 rows=1 width=49) (actual time=11,032,460.095..11,032,460.114 rows=17 loops=1)

  • Buffers: shared hit=56,299,635 read=13
37. 0.034 0.034 ↑ 1.0 1 17

Index Scan using userinfo_pkey on userinfo ui (cost=0.29..2.30 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = dmv_timesheetday_facts7.userid1)
  • Buffers: shared hit=51
38. 0.034 0.034 ↑ 1.0 1 17

Index Scan using login_pkey on login (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=51
39. 0.034 0.034 ↑ 1.0 1 17

Index Scan using clients_pkey on clients cl (cost=0.27..2.29 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (dmv_timesheetday_facts7.clientid6 = id)
  • Buffers: shared hit=51
40. 0.034 0.034 ↑ 1.0 1 17

Index Scan using ixduuserid on departmentusers du (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=51
41. 0.034 0.034 ↑ 1.0 1 17

Index Scan using departments_pkey on departments dep (cost=0.14..0.16 rows=1 width=520) (actual time=0.002..0.002 rows=1 loops=17)

  • Index Cond: (id = du.departmentid)
  • Buffers: shared hit=34
Planning time : 5.389 ms
Execution time : 11,032,460.882 ms