Bug #9465: Super slow query on dashboard loading - Rudder - Issue Tracker

Actions

Copy link

Bug #9465

closed

User story #1808: Add an Audit mode to Rudder: only check properties, no modification on nodes

Super slow query on dashboard loading

Added by Nicolas CHARLES about 8 years ago. Updated about 8 years ago.

Status:

Released

Priority:

N/A

Assignee:

François ARMAND

Category:

Web - Compliance & node report

Target version:

4.0.0~rc1

Pull Request:

https://github.com/Normation/rudder/p...

Severity:

UX impact:

User visibility:

Effort required:

Priority:

Name check:

Fix check:

Regression:

Description

We have the following query on the dashboard loading (or compliance computation)

explain analyze select distinct on (r.nodeid)
                                 r.nodeid, r.date, r.nodeconfigid, r.complete, r.insertionid
                               , c.nodeid, c.nodeconfigid, c.begindate, c.enddate, c.configuration
                               from  reportsexecution r
                               left outer join nodeconfigurations c
                                 on r.nodeId = c.nodeid and r.nodeconfigid = c.nodeconfigid
                               where r.complete = true order by r.nodeid, r.insertionid desc;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=95777.70..96086.82 rows=505 width=1343) (actual time=20555.051..22843.026 rows=512 loops=1)
   ->  Sort  (cost=95777.70..95932.26 rows=61824 width=1343) (actual time=20554.875..22751.038 rows=861611 loops=1)
         Sort Key: r.nodeid, r.insertionid DESC
         Sort Method: external merge  Disk: 1051800kB
         ->  Hash Left Join  (cost=8417.25..18582.78 rows=61824 width=1343) (actual time=77.136..733.637 rows=861611 loops=1)
               Hash Cond: ((r.nodeid = c.nodeid) AND (r.nodeconfigid = c.nodeconfigid))
               ->  Seq Scan on reportsexecution r  (cost=0.00..1399.66 rows=61824 width=61) (actual time=0.010..13.283 rows=61854 loops=1)
                     Filter: complete
                     Rows Removed by Filter: 446
               ->  Hash  (cost=4856.30..4856.30 rows=20330 width=1282) (actual time=76.851..76.851 rows=21356 loops=1)
                     Buckets: 4096  Batches: 8  Memory Usage: 3990kB
                     ->  Seq Scan on nodeconfigurations c  (cost=0.00..4856.30 rows=20330 width=1282) (actual time=0.282..40.486 rows=21356 loops=1)
 Planning time: 1.054 ms
 Execution time: 22846.764 ms