Actions
Bug #8057
closed
Queries on expected reports are really slow with more than 2000 nodes
Status:
Released
Priority:
N/A
Assignee:
Category:
Performance and scalability
Target version:
Pull Request:
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
Updated by Nicolas CHARLES over 8 years ago
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;
to
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 &&)
Updated by Vincent MEMBRÉ over 8 years ago
- Target version changed from 3.1.9 to 3.0.14
Updated by François ARMAND over 8 years ago
- Status changed from New to In progress
- Assignee set to François ARMAND
Updated by François ARMAND over 8 years ago
- Status changed from In progress to Pending technical review
- Assignee changed from François ARMAND to Nicolas CHARLES
- Pull Request set to https://github.com/Normation/rudder/pull/1060
Updated by François ARMAND over 8 years ago
- Status changed from Pending technical review to Pending release
- % Done changed from 0 to 100
Applied in changeset rudder|8ebf33446881e868334de0fa38318ca412d7f5c6.
Updated by Vincent MEMBRÉ over 8 years ago
- Status changed from Pending release to Released
Actions