Merging performance branch into master (#4268)

* improve performance on sendNotifications

* Fix SQL queries

* Remove get direct profiles, not needed anymore

* Add raw data to error details if AppError fails to decode

* men

* Fix decode (#4052)

* Fixing json decode

* Adding unit test

* Initial work for client scaling (#4051)

* Begin adding paging to profiles API

* Added more paging functionality

* Finish hooking up admin console user lists

* Add API for searching users and add searching to all user lists

* Add lazy loading of profiles

* Revert config.json

* Fix unit tests and some style issues

* Add GetProfilesFromList to Go driver and fix web unit test

* Update etag for GetProfiles

* Updating ui for filters and pagination (#4044)

* Updating UI for pagination

* Adjusting margins for filter row

* Adjusting margin for specific modals

* Adding relative padding to system console

* Adjusting responsive view

* Update client user tests

* Minor fixes for direct messages modal (#4056)

* Remove some unneeded initial load calls (#4057)

* UX updates to user lists, added smart counts and bug fixes (#4059)

* Improved getExplicitMentions and unit tests (#4064)

* Refactor getting posts to lazy load profiles correctly (#4062)

* Comment out SetActiveChannel test (#4066)

* Profiler cpu, block, and memory profiler. (#4081)

* Fix TestSetActiveChannel unit test (#4071)

* Fixing build failure caused by dependancies updating (#4076)

* Adding profiler

* Fix admin_team_member_dropdown eslint errors

* Bumping session cache size (#4077)

* Bumping session cache size

* Bumping status cache

* Refactor how the client handles channel members to be large team friendly (#4106)

* Refactor how the client handles channel members to be large team friendly

* Change Id to ChannelId in ChannelStats model

* Updated getChannelMember and getProfilesByIds routes to match proposal

* Performance improvements (#4100)

* Performance improvements

* Fixing re-connect issue

* Fixing error message

* Some other minor perf tweaks

* Some other minor perf tweaks

* Fixing config file

* Fixing buffer size

* Fixing web socket send message

* adding some error logging

* fix getMe to be user required

* Fix websocket event for new user

* Fixing shutting down

* Reverting web socket changes

* Fixing logging lvl

* Adding caching to GetMember

* Adding some logging

* Fixing caching

* Fixing caching invalidate

* Fixing direct message caching

* Fixing caching

* Fixing caching

* Remove GetDirectProfiles from initial load

* Adding logging and fixing websocket client

* Adding back caching from bad merge.

* Explicitly close go driver requests (#4162)

* Refactored how the client handles team members to be more large team friendly (#4159)

* Refactor getProfilesForDirectMessageList API into getAllProfiles API

* Refactored how the client handles team members to be more large team friendly

* Fix js error when receiving a notification

* Fix JS error caused by current user being overwritten with sanitized version (#4165)

* Adding error message to status failure (#4167)

* Fix a few bugs caused by client scaling refactoring (#4170)

* When there is no read replica, don't open a second set of connections to the master database (#4173)

* Adding connection tacking to stats (#4174)

* Reduce DB writes for statuses and other status related changes (#4175)

* Fix bug preventing opening of DM channels from more modal (#4181)

* 	Fixing socket timing error (#4183)

* Fixing ping/pong handler

* Fixing socket timing error

* Commenting out status broadcasting

* Removing user status changes

* Removing user status changes

* Removing user status changes

* Removing user status changes

* Adding DoPreComputeJson()

* Performance improvements (#4194)

* * Fix System Console Analytics queries
* Add db.SetConnMaxLifetime to 15 minutes
* Add "net/http/pprof" for profiling
* Add FreeOSMemory() to manually release memory on reload config

* Add flag to enable http profiler

* Fix memory leak (#4197)

* Fix memory leak

* removed unneeded nil assignment

* Fixing go routine leak (#4208)

* Merge fixes

* Merge fix

* Refactored statuses to be queried by the client rather than broadcast by the server (#4212)

* Refactored server code to reduce status broadcasts and to allow getting statuses by IDs

* Refactor client code to periodically fetch statuses

* Add store unit test for getting statuses by ids

* Fix status unit test

* Add getStatusesByIds REST API and move the client over to use that instead of the WebSocket

* Adding multiple threads to websocket hub (#4230)

* Adding multiple threads to websocket hub

* Fixing unit tests

* Fixing so websocket connections from the same user end up in the same… (#4240)

* Fixing so websocket connections from the same user end up in the same list

* Removing old comment

* Refactor user autocomplete to query the server (#4239)

* Add API for autocompleting users

* Converted at mention autocomplete to query server

* Converted user search autocomplete to query server

* Switch autocomplete API naming to use term instead of username

* Split autocomplete API into two, one for channels and for teams

* Fix copy/paste error

* Some final client scaling fixes (#4246)

* Add lazy loading of profiles to integration pages

* Add lazy loading of profiles to emoji page

* Fix JS error when receiving post in select team menu and also clean up channel store

1 files changed, 26 insertions, 44 deletions

diff --git a/store/sql_post_store.go b/store/sql_post_store.go
index ec8679b31..44ffb556e 100644
--- a/store/sql_post_store.go
+++ b/store/sql_post_store.go
@@ -43,6 +43,7 @@ func (s SqlPostStore) CreateIndexesIfNotExists() {
 	s.CreateIndexIfNotExists("idx_posts_create_at", "Posts", "CreateAt")
 	s.CreateIndexIfNotExists("idx_posts_channel_id", "Posts", "ChannelId")
 	s.CreateIndexIfNotExists("idx_posts_root_id", "Posts", "RootId")
+	s.CreateIndexIfNotExists("idx_posts_user_id", "Posts", "UserId")
 
 	s.CreateFullTextIndexIfNotExists("idx_posts_message_txt", "Posts", "Message")
 	s.CreateFullTextIndexIfNotExists("idx_posts_hashtags_txt", "Posts", "Hashtags")
@@ -811,47 +812,36 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan
 		result := StoreResult{}
 
 		query :=
-			`SELECT
-			    t1.Name, COUNT(t1.UserId) AS Value
-			FROM
-			    (SELECT DISTINCT
+			`SELECT DISTINCT
 			        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
-			            Posts.UserId
-			    FROM
-			        Posts, Channels
-			    WHERE
-			        Posts.ChannelId = Channels.Id`
+			        COUNT(DISTINCT Posts.UserId) AS Value
+			FROM Posts
+            		INNER JOIN Channels
+			ON Posts.ChannelId = Channels.Id`
 
 		if len(teamId) > 0 {
 			query += " AND Channels.TeamId = :TeamId"
 		}
 
 		query += ` AND Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
-			    ORDER BY Name DESC) AS t1
-			GROUP BY Name
+			GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
 			ORDER BY Name DESC
 			LIMIT 30`
 
 		if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
 			query =
 				`SELECT
-				    TO_CHAR(t1.Name, 'YYYY-MM-DD') AS Name, COUNT(t1.UserId) AS Value
-				FROM
-				    (SELECT DISTINCT
-				        DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name,
-				            Posts.UserId
-				    FROM
-				        Posts, Channels
-				    WHERE
-				        Posts.ChannelId = Channels.Id`
+					TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, COUNT(DISTINCT Posts.UserId) AS Value
+				FROM Posts
+				INNER JOIN Channels
+				ON Posts.ChannelId = Channels.Id`
 
 			if len(teamId) > 0 {
 				query += " AND Channels.TeamId = :TeamId"
 			}
 
 			query += ` AND Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
-				    ORDER BY Name DESC) AS t1
-				GROUP BY Name
+				GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
 				ORDER BY Name DESC
 				LIMIT 30`
 		}
@@ -884,15 +874,12 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel {
 		result := StoreResult{}
 
 		query :=
-			`SELECT 
-			    Name, COUNT(Value) AS Value
-			FROM
-			    (SELECT 
+			`SELECT
 			        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
-			            '1' AS Value
-			    FROM
-			        Posts, Channels
-			    WHERE
+			        COUNT(Posts.Id) AS Value
+			    FROM Posts
+			    INNER JOIN Channels
+			    ON
 			        Posts.ChannelId = Channels.Id`
 
 		if len(teamId) > 0 {
@@ -900,31 +887,26 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel {
 		}
 
 		query += ` AND Posts.CreateAt <= :EndTime
-			            AND Posts.CreateAt >= :StartTime) AS t1
-			GROUP BY Name
+			            AND Posts.CreateAt >= :StartTime
+			GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
 			ORDER BY Name DESC
 			LIMIT 30`
 
 		if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
 			query =
-				`SELECT 
-				    Name, COUNT(Value) AS Value
-				FROM
-				    (SELECT 
-				        TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name,
-				            '1' AS Value
-				    FROM
-				        Posts, Channels
-				    WHERE
-				        Posts.ChannelId = Channels.Id`
+				`SELECT
+					TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, Count(Posts.Id) AS Value
+				FROM Posts
+				INNER JOIN Channels
+				ON Posts.ChannelId = Channels.Id`
 
 			if len(teamId) > 0 {
 				query += " AND Channels.TeamId = :TeamId"
 			}
 
 			query += ` AND Posts.CreateAt <= :EndTime
-				            AND Posts.CreateAt >= :StartTime) AS t1
-				GROUP BY Name
+				            AND Posts.CreateAt >= :StartTime
+				GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
 				ORDER BY Name DESC
 				LIMIT 30`
 		}