Project

General

Profile

Actions

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 over 7 years ago. Updated over 7 years ago.

Status:
Released
Priority:
N/A
Category:
Web - Compliance & node report
Target version:
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

Actions #2

Updated by Nicolas CHARLES over 7 years ago

  • Status changed from New to In progress
  • Assignee set to Nicolas CHARLES
Actions #3

Updated by Nicolas CHARLES over 7 years ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from Nicolas CHARLES to François ARMAND
  • Pull Request set to https://github.com/Normation/rudder/pull/1308
Actions #4

Updated by Nicolas CHARLES over 7 years ago

  • Status changed from Pending technical review to Pending release
  • % Done changed from 0 to 100
Actions #5

Updated by Vincent MEMBRÉ over 7 years ago

  • Target version changed from 318 to 4.0.0~rc2
Actions #6

Updated by Vincent MEMBRÉ over 7 years ago

  • Target version changed from 4.0.0~rc2 to 4.0.0~rc1
Actions #7

Updated by Vincent MEMBRÉ over 7 years ago

  • Parent task set to #1808
Actions #8

Updated by Alexis Mousset over 7 years ago

  • Status changed from Pending release to Released

This bug has been fixed in Rudder 4.0.0 which was released the 10th November 2016.

Actions

Also available in: Atom PDF