explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BJVV

Settings
# exclusive inclusive rows x rows loops node
1. 141.956 44,610.215 ↓ 179.9 21,414 1

Sort (cost=2,259,215.95..2,259,216.25 rows=119 width=321) (actual time=44,601.604..44,610.215 rows=21,414 loops=1)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts5.userduplicatename3, ((login.loginname)::character varying(255)) COLLATE "en_US", ((userinfo7.displayname)::text) COLLATE "en_US", dmv_timesheetday_facts5.timesheetstartdate4, dmv_timesheetday_facts5.timesheetenddate5, dmv_timesheetday_facts5.entrydate2, ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ui.id, ts.id
  • Sort Method: quicksort Memory: 6148kB
  • Buffers: shared hit=703594 read=541323
2.          

CTE dmv_timesheetday_facts0cte

3. 46.850 43,898.988 ↑ 1.1 21,431 1

HashAggregate (cost=2,257,893.14..2,258,130.93 rows=23,779 width=61) (actual time=43,881.691..43,898.988 rows=21,431 loops=1)

  • Group Key: ts_1.userid, at.entrydate, ui_1.duplicatename, ts_2.startdate, ts_2.enddate, tslist_1.timesheetstatus, at.projectid, at.taskid, ts_1.id
  • Buffers: shared hit=311574 read=541323
4. 33.347 43,852.138 ↑ 1.1 21,696 1

Nested Loop (cost=282,441.39..2,257,298.67 rows=23,779 width=61) (actual time=18,384.170..43,852.138 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = tslist_1.timesheetid)
  • Buffers: shared hit=311574 read=541323
5. 41.619 43,753.703 ↑ 1.1 21,696 1

Nested Loop (cost=282,440.96..2,245,356.42 rows=23,779 width=89) (actual time=18,384.156..43,753.703 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = ts_2.id)
  • Buffers: shared hit=224737 read=541323
6. 589.250 43,646.996 ↑ 1.1 21,696 1

Hash Join (cost=282,440.54..2,234,336.03 rows=23,779 width=65) (actual time=18,384.121..43,646.996 rows=21,696 loops=1)

  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=137892 read=541323
7. 5,590.449 43,026.816 ↓ 1.0 1,581,081 1

Hash Left Join (cost=273,604.68..2,203,535.09 rows=1,580,167 width=97) (actual time=7,627.039..43,026.816 rows=1,581,081 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=118729 read=541323
8. 808.675 37,436.336 ↓ 1.0 1,581,081 1

Hash Left Join (cost=273,588.32..1,260,190.06 rows=1,580,167 width=65) (actual time=7,626.945..37,436.336 rows=1,581,081 loops=1)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=118716 read=541323
9. 4,704.515 36,627.647 ↓ 1.0 1,581,081 1

Hash Right Join (cost=273,581.81..1,248,332.28 rows=1,580,167 width=69) (actual time=7,626.911..36,627.647 rows=1,581,081 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=118715 read=541323
10. 29,614.081 29,614.081 ↓ 54.8 7,873,964 1

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..973,783.74 rows=143,590 width=17) (actual time=172.959..29,614.081 rows=7,873,964 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 20892681
  • Buffers: shared hit=1692 read=541323
11. 769.601 2,309.051 ↓ 1.0 1,581,081 1

Hash (cost=253,829.72..253,829.72 rows=1,580,167 width=84) (actual time=2,309.051..2,309.051 rows=1,581,081 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 175284kB
  • Buffers: shared hit=117023
12. 1,539.450 1,539.450 ↓ 1.0 1,581,081 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..253,829.72 rows=1,580,167 width=84) (actual time=9.788..1,539.450 rows=1,581,081 loops=1)

  • Filter: ((entrydate >= '2018-01-20'::date) AND (entrydate <= '2018-08-08'::date))
  • Rows Removed by Filter: 7548840
  • Buffers: shared hit=117023
13. 0.001 0.014 ↓ 0.0 0 1

Hash (cost=6.44..6.44 rows=5 width=16) (actual time=0.014..0.014 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
14. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.15..6.44 rows=5 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=1
15. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..1.15 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 12
  • Buffers: shared hit=1
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.15..5.24 rows=5 width=16) (never executed)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
17. 0.001 0.031 ↓ 0.0 0 1

Hash (cost=15.60..15.60 rows=61 width=44) (actual time=0.031..0.031 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=13
18. 0.007 0.030 ↓ 0.0 0 1

Nested Loop (cost=0.15..15.60 rows=61 width=44) (actual time=0.030..0.030 rows=0 loops=1)

  • Buffers: shared hit=13
19. 0.011 0.011 ↑ 1.0 12 1

Seq Scan on project pj_1 (cost=0.00..1.15 rows=12 width=4) (actual time=0.005..0.011 rows=12 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=1
20. 0.012 0.012 ↓ 0.0 0 12

Index Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.15..1.15 rows=5 width=44) (actual time=0.001..0.001 rows=0 loops=12)

  • Index Cond: (projectid = pj_1.id)
  • Buffers: shared hit=12
21. 4.835 30.930 ↑ 1.1 12,082 1

Hash (cost=8,670.09..8,670.09 rows=13,261 width=21) (actual time=30.930..30.930 rows=12,082 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 754kB
  • Buffers: shared hit=19163
22. 8.117 26.095 ↑ 1.1 12,082 1

Merge Join (cost=0.82..8,670.09 rows=13,261 width=21) (actual time=0.417..26.095 rows=12,082 loops=1)

  • Merge Cond: (ui_1.id = ts_1.userid)
  • Buffers: shared hit=19163
23. 4.881 4.881 ↑ 1.0 7,089 1

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..393.51 rows=7,215 width=5) (actual time=0.006..4.881 rows=7,089 loops=1)

  • Buffers: shared hit=6835
24. 13.097 13.097 ↑ 1.1 12,082 1

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.42..8,094.21 rows=13,261 width=20) (actual time=0.016..13.097 rows=12,082 loops=1)

  • Index Cond: (userid = ANY ('{3087,3094,3104,7061,6789,948,1303,804,815,7086,2738,341,3279,3308,3684,861,873,6747,886,3334,898,3362,7116,373,916,155,929,2771,939,953,966,981,775,5451,997,1007,7197,3417,1023,2366,1034,1045,1060,1071,1082,3078,1110,1124,787,1137,1149,1160,1173,1185,1194,414,1210,1223,1237,1251,1265,1277,1290,1729,746,1315,1328,1350,447,6866,1365,1384,1397,1409,807,1423,2281,1452,1463,482,820,1473,1486,1498,1511,1522,1533,835,1549,1564,1575,1589,1602,5383,1629,1642,3444,662,509,1655}'::integer[]))
  • Buffers: shared hit=12328
25. 65.088 65.088 ↑ 1.0 1 21,696

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

  • Index Cond: (id = at.timesheetid)
  • Buffers: shared hit=86845
26. 65.088 65.088 ↑ 1.0 1 21,696

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist_1 (cost=0.42..0.49 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=21,696)

  • Index Cond: (timesheetid = at.timesheetid)
  • Buffers: shared hit=86837
27.          

Initplan (forSort)

28. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Buffers: shared hit=1
29. 25.520 44,468.256 ↓ 179.9 21,414 1

Nested Loop (cost=21.99..1,079.91 rows=119 width=321) (actual time=43,882.190..44,468.256 rows=21,414 loops=1)

  • Buffers: shared hit=703594 read=541323
30. 19.964 44,357.080 ↓ 179.9 21,414 1

Nested Loop (cost=21.57..1,024.04 rows=119 width=337) (actual time=43,882.176..44,357.080 rows=21,414 loops=1)

  • Buffers: shared hit=633792 read=541323
31. 14.854 44,251.460 ↓ 179.9 21,414 1

Hash Left Join (cost=21.14..852.19 rows=119 width=321) (actual time=43,882.165..44,251.460 rows=21,414 loops=1)

  • Hash Cond: (tk.id = tdh.taskid)
  • Buffers: shared hit=569469 read=541323
32. 14.677 44,236.484 ↓ 179.9 21,414 1

Hash Left Join (cost=10.72..840.14 rows=119 width=259) (actual time=43,882.026..44,236.484 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.taskid8 = tk.id)
  • Buffers: shared hit=569462 read=541323
33. 14.314 44,221.586 ↓ 179.9 21,414 1

Hash Left Join (cost=2.40..830.46 rows=119 width=233) (actual time=43,881.790..44,221.586 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.projectid7 = pj.id)
  • Buffers: shared hit=569457 read=541323
34. 31.683 44,207.256 ↓ 179.9 21,414 1

Nested Loop Left Join (cost=1.13..828.68 rows=119 width=99) (actual time=43,881.751..44,207.256 rows=21,414 loops=1)

  • Buffers: shared hit=569456 read=541323
35. 30.054 44,132.745 ↓ 179.9 21,414 1

Nested Loop Left Join (cost=0.85..787.04 rows=119 width=88) (actual time=43,881.743..44,132.745 rows=21,414 loops=1)

  • Buffers: shared hit=504948 read=541323
36. 26.985 44,059.863 ↓ 179.9 21,414 1

Nested Loop (cost=0.56..748.17 rows=119 width=84) (actual time=43,881.729..44,059.863 rows=21,414 loops=1)

  • Join Filter: (dmv_timesheetday_facts5.userid1 = login.userid)
  • Buffers: shared hit=440366 read=541323
37. 27.330 43,990.050 ↓ 179.9 21,414 1

Nested Loop (cost=0.28..707.92 rows=119 width=78) (actual time=43,881.717..43,990.050 rows=21,414 loops=1)

  • Buffers: shared hit=376068 read=541323
38. 43,919.892 43,919.892 ↓ 179.9 21,414 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..535.03 rows=119 width=61) (actual time=43,881.705..43,919.892 rows=21,414 loops=1)

  • Filter: (timesheetstatus6 = 2)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=311574 read=541323
39. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..1.44 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: (id = dmv_timesheetday_facts5.userid1)
  • Buffers: shared hit=64494
40. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using login_pkey on login (cost=0.28..0.33 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=64298
41. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy12 (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: ((ui.id = userid) AND ('2019-01-20'::date >= startdate))
  • Filter: ('2019-01-20'::date <= enddate)
  • Buffers: shared hit=64582
42. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using userinfo_pkey on userinfo userinfo7 (cost=0.28..0.34 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=21,414)

  • Index Cond: (userhierarchy12.supervisorid = id)
  • Buffers: shared hit=64508
43. 0.006 0.016 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=138) (actual time=0.016..0.016 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
44. 0.010 0.010 ↑ 1.0 12 1

Seq Scan on project pj (cost=0.00..1.12 rows=12 width=138) (actual time=0.004..0.010 rows=12 loops=1)

  • Buffers: shared hit=1
45. 0.066 0.221 ↑ 1.0 152 1

Hash (cost=6.42..6.42 rows=152 width=30) (actual time=0.221..0.221 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=5
46. 0.089 0.155 ↑ 1.0 152 1

Hash Left Join (cost=1.13..6.42 rows=152 width=30) (actual time=0.049..0.155 rows=152 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=5
47. 0.039 0.039 ↑ 1.0 152 1

Seq Scan on task tk (cost=0.00..4.52 rows=152 width=34) (actual time=0.004..0.039 rows=152 loops=1)

  • Buffers: shared hit=3
48. 0.004 0.027 ↑ 1.0 2 1

Hash (cost=1.11..1.11 rows=2 width=4) (actual time=0.027..0.027 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
49. 0.023 0.023 ↑ 1.0 2 1

Seq Scan on exchangerate (cost=0.00..1.11 rows=2 width=4) (actual time=0.022..0.023 rows=2 loops=1)

  • Filter: ((variablecurrencyid = $4) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2
50. 0.065 0.122 ↑ 1.0 152 1

Hash (cost=8.52..8.52 rows=152 width=66) (actual time=0.122..0.122 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=7
51. 0.057 0.057 ↑ 1.0 152 1

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..8.52 rows=152 width=66) (actual time=0.003..0.057 rows=152 loops=1)

  • Buffers: shared hit=7
52. 85.656 85.656 ↑ 1.0 1 21,414

Index Only Scan using timesheet_pkey on timesheet ts (cost=0.42..1.43 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=21,414)

  • Index Cond: (id = dmv_timesheetday_facts5.timesheetid9)
  • Heap Fetches: 19
  • Buffers: shared hit=64323
53. 85.656 85.656 ↑ 1.0 1 21,414

Index Only Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..0.46 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=21,414)

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 4915
  • Buffers: shared hit=69802
Planning time : 7.686 ms
Execution time : 44,615.827 ms