refactor(tui.analytics): improve query for listing sites

commit: 2ba02ac
parent: 21ef055
author: Eric Bower
date: 2025-04-10 11:33:47 -0400 EDT

refactor(tui.analytics): improve query for listing sites

3 files changed, +4, -2

M Makefile

M pkg/db/postgres/storage.go

A sql/migrations/20250410_add_index_analytics_visits_host_list.sql

M Makefile

+2, -1

 1@@ -128,10 +128,11 @@ migrate:
 2 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20241202_add_more_idx_analytics.sql
 3 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250319_add_tuns_event_logs_table.sql
 4 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_tunnel_id_to_tuns_event_logs_table.sql
 5+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250410_add_index_analytics_visits_host_list.sql
 6 .PHONY: migrate
 7 
 8 latest:
 9-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250320_add_tunnel_id_to_tuns_event_logs_table.sql
10+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20250410_add_index_analytics_visits_host_list.sql
11 .PHONY: latest
12 
13 psql:

M pkg/db/postgres/storage.go

+1, -1

1@@ -1135,7 +1135,7 @@ func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
2 		host,
3 		count(DISTINCT ip_address) as host_count
4 	FROM analytics_visits
5-	WHERE user_id = $1 AND host <> '' AND status >= 200 AND status < 300
6+	WHERE user_id = $1 AND host <> ''
7 	GROUP BY host
8 	ORDER BY host_count DESC`
9

A sql/migrations/20250410_add_index_analytics_visits_host_list.sql

+1, -0

1@@ -0,0 +1 @@
2+CREATE INDEX CONCURRENTLY analytics_visits_user_id_host_ip_address_idx ON analytics_visits (user_id, host, ip_address);