search

openwisp / openwisp-monitoring

Network monitoring system written in Python and Django, designed to be extensible, programmable, scalable and easy to use by end users: once the system is configured, monitoring checks, alerts and metric collection happens automatically.
https://openwisp.io/docs/dev/monitoring/
Other
165 stars 112 forks source link

[bug] Slow WiFi Session dashboard chart database query #528

Closed pandafy closed 1 year ago

pandafy commented 1 year ago

The dashboard webpage fails to load when there are large number of devices and active WiFi Sessions.

We need to optimize the query for fetching count of active WiFi sessions. Currently, the following SQL query is getting executed: 

SELECT SUM("active") FROM (SELECT COUNT(CASE WHEN "device_monitoring_wifisession"."stop_time" IS NULL THEN %s ELSE NULL END) AS "active" FROM "device_monitoring_wifisession" GROUP BY "device_monitoring_wifisession"."id") subquery;

The following is output of ANALYZE EXPLAIN: 

 Aggregate  (cost=204003.23..204003.24 rows=1 width=32) (actual time=80571.469..80571.472 rows=1 loops=1)
   ->  GroupAggregate  (cost=0.43..166125.92 rows=3030185 width=24) (actual time=493.846..80195.169 rows=3011150 loops=1)
         Group Key: device_monitoring_wifisession.id
         ->  Index Scan using device_monitoring_wifisession_pkey on device_monitoring_wifisession  (cost=0.43..120673.14 rows=3030185 width=24) (actual time=3.448..77936.727 rows=3011150 loops=1)
 Planning Time: 0.154 ms
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 35.183 ms, Inlining 0.000 ms, Optimization 80.170 ms, Emission 428.688 ms, Total 544.041 ms
 Execution Time: 80616.425 ms
(10 rows)