explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 523j

Settings
# exclusive inclusive rows x rows loops node
1. 141.909 19,467.226 ↓ 179.9 21,414 1

Sort (cost=1,939,119.48..1,939,119.78 rows=119 width=321) (actual time=19,458.924..19,467.226 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: 6153kB
  • Buffers: shared hit=1609817
2.          

CTE dmv_timesheetday_facts0cte

3. 47.312 18,773.827 ↑ 1.1 21,431 1

HashAggregate (cost=1,937,825.10..1,938,065.04 rows=23,994 width=61) (actual time=18,756.473..18,773.827 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=1201102
4. 28.484 18,726.515 ↑ 1.1 21,696 1

Nested Loop (cost=85,717.90..1,937,225.25 rows=23,994 width=61) (actual time=2,905.456..18,726.515 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = ts_2.id)
  • Buffers: shared hit=1201102
5. 38.995 18,632.943 ↑ 1.1 21,696 1

Nested Loop (cost=85,717.47..1,926,122.19 rows=23,994 width=85) (actual time=2,905.444..18,632.943 rows=21,696 loops=1)

  • Join Filter: (ts_1.id = tslist_1.timesheetid)
  • Buffers: shared hit=1114151
6. 640.618 18,528.860 ↑ 1.1 21,696 1

Hash Join (cost=85,717.05..1,914,289.50 rows=24,111 width=65) (actual time=2,905.425..18,528.860 rows=21,696 loops=1)

  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=1027205
7. 7,506.977 17,860.921 ↑ 1.0 1,581,081 1

Hash Left Join (cost=77,107.28..1,883,674.06 rows=1,582,877 width=97) (actual time=2,141.561..17,860.921 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=1008468
8. 863.819 10,353.916 ↑ 1.0 1,581,081 1

Hash Left Join (cost=77,090.92..938,192.74 rows=1,582,877 width=65) (actual time=2,141.475..10,353.916 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=1008455
9. 4,440.178 9,490.084 ↑ 1.0 1,581,081 1

Hash Right Join (cost=77,084.41..926,314.64 rows=1,582,877 width=69) (actual time=2,141.447..9,490.084 rows=1,581,081 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=1008454
10. 2,910.221 2,910.221 ↑ 1.0 7,214,206 1

Index Scan using dbatest on dm_attendancetimeallocation_metadata isbillable (cost=0.56..799,465.78 rows=7,215,403 width=17) (actual time=0.031..2,910.221 rows=7,214,206 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Buffers: shared hit=217478
11. 812.291 2,139.685 ↑ 1.0 1,581,081 1

Hash (cost=57,297.88..57,297.88 rows=1,582,877 width=84) (actual time=2,139.685..2,139.685 rows=1,581,081 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 175283kB
  • Buffers: shared hit=790976
12. 1,327.394 1,327.394 ↑ 1.0 1,581,081 1

Index Scan using dbatest1 on dm_attendancetimeallocation_facts at (cost=0.43..57,297.88 rows=1,582,877 width=84) (actual time=0.013..1,327.394 rows=1,581,081 loops=1)

  • Index Cond: ((entrydate >= '2018-01-20'::date) AND (entrydate <= '2018-08-08'::date))
  • Buffers: shared hit=790976
13. 0.001 0.013 ↓ 0.0 0 1

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

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

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

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

Seq Scan on project pj_2 (cost=0.00..1.15 rows=1 width=4) (actual time=0.011..0.011 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.028 ↓ 0.0 0 1

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

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

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

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

Seq Scan on project pj_1 (cost=0.00..1.15 rows=12 width=4) (actual time=0.005..0.010 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.595 27.321 ↑ 1.1 11,738 1

Hash (cost=8,446.82..8,446.82 rows=13,036 width=21) (actual time=27.321..27.321 rows=11,738 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 736kB
  • Buffers: shared hit=18737
22. 8.010 22.726 ↑ 1.1 11,738 1

Merge Join (cost=0.84..8,446.82 rows=13,036 width=21) (actual time=0.348..22.726 rows=11,738 loops=1)

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

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..294.95 rows=7,178 width=5) (actual time=0.010..4.279 rows=7,089 loops=1)

  • Buffers: shared hit=6761
24. 10.437 10.437 ↑ 1.1 11,738 1

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.42..7,971.58 rows=13,036 width=20) (actual time=0.012..10.437 rows=11,738 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=11976
25. 65.088 65.088 ↑ 1.0 1 21,696

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

  • Index Cond: (timesheetid = at.timesheetid)
  • Buffers: shared hit=86946
26. 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=86951
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. 18.943 19,325.314 ↓ 179.9 21,414 1

Hash Left Join (cost=19.99..1,049.32 rows=119 width=321) (actual time=18,756.942..19,325.314 rows=21,414 loops=1)

  • Hash Cond: (tk.id = tdh.taskid)
  • Buffers: shared hit=1609817
30. 16.821 19,306.252 ↓ 179.9 21,414 1

Hash Left Join (cost=11.57..1,039.27 rows=119 width=259) (actual time=18,756.807..19,306.252 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.taskid8 = tk.id)
  • Buffers: shared hit=1609812
31. 18.256 19,289.211 ↓ 179.9 21,414 1

Nested Loop (cost=3.25..1,029.60 rows=119 width=233) (actual time=18,756.572..19,289.211 rows=21,414 loops=1)

  • Buffers: shared hit=1609807
32. 29.096 19,185.299 ↓ 178.4 21,414 1

Nested Loop (cost=2.82..967.32 rows=120 width=249) (actual time=18,756.561..19,185.299 rows=21,414 loops=1)

  • Buffers: shared hit=1523990
33. 15.125 19,091.961 ↓ 178.4 21,414 1

Hash Left Join (cost=2.40..783.02 rows=120 width=233) (actual time=18,756.552..19,091.961 rows=21,414 loops=1)

  • Hash Cond: (dmv_timesheetday_facts5.projectid7 = pj.id)
  • Buffers: shared hit=1459580
34. 30.136 19,076.822 ↓ 178.4 21,414 1

Nested Loop Left Join (cost=1.13..781.23 rows=120 width=99) (actual time=18,756.524..19,076.822 rows=21,414 loops=1)

  • Buffers: shared hit=1459579
35. 29.922 19,003.858 ↓ 178.4 21,414 1

Nested Loop Left Join (cost=0.85..740.88 rows=120 width=88) (actual time=18,756.517..19,003.858 rows=21,414 loops=1)

  • Buffers: shared hit=1395071
36. 23.382 18,931.108 ↓ 178.4 21,414 1

Nested Loop (cost=0.56..701.69 rows=120 width=84) (actual time=18,756.505..18,931.108 rows=21,414 loops=1)

  • Join Filter: (dmv_timesheetday_facts5.userid1 = ui.id)
  • Buffers: shared hit=1330090
37. 27.112 18,864.898 ↓ 178.4 21,414 1

Nested Loop (cost=0.28..661.07 rows=120 width=71) (actual time=18,756.495..18,864.898 rows=21,414 loops=1)

  • Buffers: shared hit=1265596
38. 18,794.958 18,794.958 ↓ 178.4 21,414 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts5 (cost=0.00..539.87 rows=120 width=61) (actual time=18,756.486..18,794.958 rows=21,414 loops=1)

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

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

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

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

  • Index Cond: (id = login.userid)
  • Buffers: shared hit=64494
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=64981
42. 42.828 42.828 ↑ 1.0 1 21,414

Index Scan using userinfo_pkey on userinfo userinfo7 (cost=0.28..0.33 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.014 ↑ 1.0 12 1

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

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

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

  • Buffers: shared hit=1
45. 64.242 64.242 ↑ 1.0 1 21,414

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

  • Index Cond: (id = dmv_timesheetday_facts5.timesheetid9)
  • Heap Fetches: 0
  • Buffers: shared hit=64410
46. 85.656 85.656 ↑ 1.0 1 21,414

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

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 21414
  • Buffers: shared hit=85817
47. 0.056 0.220 ↑ 1.0 152 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
  • Buffers: shared hit=5
48. 0.101 0.164 ↑ 1.0 152 1

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

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=5
49. 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.003..0.039 rows=152 loops=1)

  • Buffers: shared hit=3
50. 0.004 0.024 ↑ 1.0 2 1

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

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

Seq Scan on exchangerate (cost=0.00..1.11 rows=2 width=4) (actual time=0.019..0.020 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
52. 0.067 0.119 ↑ 1.0 152 1

Hash (cost=6.52..6.52 rows=152 width=66) (actual time=0.119..0.119 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=5
53. 0.052 0.052 ↑ 1.0 152 1

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

  • Buffers: shared hit=5