Bug #8057: Queries on expected reports are really slow with more than 2000 nodes - Rudder - Issue Tracker

Actions

Copy link

Bug #8057

closed

Queries on expected reports are really slow with more than 2000 nodes

Bug #8057: Queries on expected reports are really slow with more than 2000 nodes

Added by Nicolas CHARLES almost 10 years ago. Updated over 9 years ago.

Status:

Released

Priority:

N/A

Assignee:

Nicolas CHARLES

Category:

Performance and scalability

Target version:

3.0.14

Pull Request:

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

Severity:

UX impact:

User visibility:

Effort required:

Priority:

Name check:

Fix check:

Regression:

Description

When we have severall thousands nodes, the queries to get expected reports in 3.0+ is too slow

There are two parts to improve, the query itself and the index

Subtasks 2 (0 open — 2 closed)

Updated by Nicolas CHARLES almost 10 years ago Actions
Copy link
#1

We need to change

 SELECT E.pkid, NNN.nodeid, NNN.nodeconfigids FROM expectedreports E INNER JOIN ( SELECT NN.nodejoinkey, NN.nodeid, NN.nodeconfigids FROM ( SELECT N.nodejoinkey, N.nodeid, N.nodeconfigids, generate_subscripts ( N.nodeconfigids, 1 ) AS v FROM expectedreportsnodes N ) AS NN WHERE NN.nodeconfigids [ v ] IN ( '-729463641', '-1360796549', '-1647020772', '-1069407658', '-169478327', '414076556', '-1160380758', '587719827', '2003792667', '-1527719623', '-2002282454', '-1391152750', '1775299637', '-327440080', '1438665908', '-1264048383', '902539539', '-1998333504', '548284150', '-1187765222', '538198140', '-1934245639', '963348761', '2141341279' ) ) AS NNN ON E.nodejoinkey = NNN.nodejoinkey;

EXPLAIN ANALYSE select
   E.pkid, NNN.nodeid, NNN.nodeconfigids
  from expectedreports E
   inner join (
                    select N.nodejoinkey, N.nodeid, N.nodeconfigids
                    from expectedreportsnodes N
                  where (nodeconfigids && '{"-729463641", "-1360796549", "-1647020772", "-1069407658", "-169478327", "414076556", "-1160380758", "587719827", "2003792667", 
                    "-1527719623", "-2002282454", "-1391152750", "1775299637", "-327440080", "1438665908", "-1264048383", "902539539", "-1998333504", "548284150", "-1187765222", "538198140", 
                    "-1934245639", "963348761", "2141341279" }')
                ) as NNN
     on E.nodejoinkey = NNN.nodejoinkey;

(replace the generate_subscript to the &&)