explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ndlp : eXcell_after_analyze

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 16,814.908 ↓ 17.0 17 1

Sort (cost=1,368,115.45..1,368,115.45 rows=1 width=2,385) (actual time=16,814.907..16,814.908 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=59,609
2.          

CTE dmv_timesheetday_facts0cte

3. 0.017 16,814.625 ↓ 17.0 17 1

GroupAggregate (cost=1,368,109.97..1,368,110.00 rows=1 width=49) (actual time=16,814.616..16,814.625 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=59,371
4. 0.018 16,814.608 ↓ 17.0 17 1

Sort (cost=1,368,109.97..1,368,109.98 rows=1 width=49) (actual time=16,814.607..16,814.608 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=59,371
5. 5,577.435 16,814.590 ↓ 17.0 17 1

Nested Loop (cost=1,509.55..1,368,109.96 rows=1 width=49) (actual time=16,626.034..16,814.590 rows=17 loops=1)

  • Join Filter: (at.timesheetid = ts.id)
  • Rows Removed by Join Filter: 74,691,028
  • Buffers: shared hit=59,371
6. 261.464 7,113.750 ↓ 2.1 52,195 1

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

  • Hash Cond: (at.projectid = project4.id)
  • Buffers: shared hit=40,483
7. 5,919.310 6,852.067 ↓ 1.0 2,330,479 1

Hash Left Join (cost=1,428.25..1,329,421.01 rows=2,330,036 width=616) (actual time=10.082..6,852.067 rows=2,330,479 loops=1)

  • 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=40,344
8. 635.970 924.799 ↓ 1.0 2,330,478 1

Hash Left Join (cost=556.62..80,672.27 rows=2,330,036 width=81) (actual time=2.050..924.799 rows=2,330,478 loops=1)

  • 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=39,814
9. 286.790 286.790 ↓ 1.0 2,330,478 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..62,640.36 rows=2,330,036 width=81) (actual time=0.005..286.790 rows=2,330,478 loops=1)

  • Buffers: shared hit=39,340
10. 0.001 2.039 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=474
12. 2.038 2.038 ↓ 0.0 0 1

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

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 6,528
  • Buffers: shared hit=474
13. 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
14. 1.171 7.958 ↓ 1.0 6,550 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 429kB
  • Buffers: shared hit=530
15. 1.398 6.787 ↓ 1.0 6,550 1

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

  • Hash Cond: (pc.projectid = pj.id)
  • Buffers: shared hit=530
16. 2.166 2.166 ↓ 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.166 rows=6,550 loops=1)

  • Buffers: shared hit=56
17. 0.890 3.223 ↓ 1.0 6,528 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 319kB
  • Buffers: shared hit=474
18. 2.333 2.333 ↓ 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.333 rows=6,528 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=474
19. 0.010 0.219 ↑ 1.0 60 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=139
20. 0.023 0.209 ↑ 1.0 60 1

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

  • Group Key: project4.id
  • Buffers: shared hit=139
21. 0.053 0.186 ↑ 1.0 60 1

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

  • Buffers: shared hit=139
22. 0.073 0.073 ↑ 1.0 60 1

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

  • Index Cond: (clientid = 656)
  • Buffers: shared hit=12
23. 0.060 0.060 ↑ 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.001..0.001 rows=1 loops=60)

  • Index Cond: (id = projectclient5.projectid)
  • Heap Fetches: 6
  • Buffers: shared hit=127
24. 4,100.951 4,123.405 ↓ 204.4 1,431 52,195

Materialize (cost=1.55..6,638.18 rows=7 width=61) (actual time=0.000..0.079 rows=1,431 loops=52,195)

  • Buffers: shared hit=18,888
25. 0.647 22.454 ↓ 204.4 1,431 1

Nested Loop (cost=1.55..6,638.15 rows=7 width=61) (actual time=0.316..22.454 rows=1,431 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=18,888
26. 0.926 17.514 ↓ 204.4 1,431 1

Nested Loop (cost=1.13..6,634.88 rows=7 width=37) (actual time=0.306..17.514 rows=1,431 loops=1)

  • Buffers: shared hit=13,164
27. 1.815 10.864 ↓ 204.4 1,431 1

Nested Loop (cost=0.71..6,623.80 rows=7 width=21) (actual time=0.289..10.864 rows=1,431 loops=1)

  • Buffers: shared hit=7,042
28. 7.618 7.618 ↓ 204.4 1,431 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..6,607.68 rows=7 width=20) (actual time=0.278..7.618 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,741
29. 1.431 1.431 ↑ 1.0 1 1,431

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

  • Index Cond: (id = ts.userid)
  • Buffers: shared hit=4,301
30. 5.724 5.724 ↑ 1.0 1 1,431

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..1.58 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,431)

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 1,415
  • Buffers: shared hit=6,122
31. 4.293 4.293 ↑ 1.0 1 1,431

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.45 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1,431)

  • Index Cond: (id = tslist.timesheetid)
  • Buffers: shared hit=5,724
32. 0.019 16,814.863 ↓ 17.0 17 1

Nested Loop (cost=1.27..5.43 rows=1 width=2,385) (actual time=16,814.686..16,814.863 rows=17 loops=1)

  • Buffers: shared hit=59,609
33. 0.013 16,814.827 ↓ 17.0 17 1

Nested Loop (cost=1.13..5.28 rows=1 width=117) (actual time=16,814.675..16,814.827 rows=17 loops=1)

  • Join Filter: (dmv_timesheetday_facts7.userid1 = du.userid)
  • Buffers: shared hit=59,575
34. 0.006 16,814.780 ↓ 17.0 17 1

Nested Loop Left Join (cost=0.84..4.95 rows=1 width=117) (actual time=16,814.663..16,814.780 rows=17 loops=1)

  • Buffers: shared hit=59,524
35. 0.009 16,814.740 ↓ 17.0 17 1

Nested Loop (cost=0.57..2.65 rows=1 width=99) (actual time=16,814.652..16,814.740 rows=17 loops=1)

  • Join Filter: (dmv_timesheetday_facts7.userid1 = login.userid)
  • Buffers: shared hit=59,473
36. 0.025 16,814.697 ↓ 17.0 17 1

Nested Loop (cost=0.29..2.32 rows=1 width=87) (actual time=16,814.640..16,814.697 rows=17 loops=1)

  • Buffers: shared hit=59,422
37. 16,814.638 16,814.638 ↓ 17.0 17 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts7 (cost=0.00..0.02 rows=1 width=49) (actual time=16,814.619..16,814.638 rows=17 loops=1)

  • Buffers: shared hit=59,371
38. 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
39. 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
40. 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
41. 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
42. 0.017 0.017 ↑ 1.0 1 17

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

  • Index Cond: (id = du.departmentid)
  • Buffers: shared hit=34
Planning time : 5.449 ms
Execution time : 16,815.396 ms