I am using sails version 0.10.5 for a particular project. We are using postgresql as the underlying database which is a few GBs in size. Recently I started noticing a problem. There is a postgres table whose model definition is as follows:
module.exports = {
attributes: {
user: {
model: "user",
required: true
},
organization: {
model: "organization",
required: true
},
questionaire: {
model: "questionaire",
required: true
},
mailinglist: {
model: "mailinglist",
defaultsTo: null
},
name: {
type: "string",
required: true
},
broadcast: {
type: "boolean",
defaultsTo: false
},
link: {
type: "string",
defaultsTo: null
},
count: {
type: "integer",
defaultsTo: 0
}
}
};
And when I use the controller's blueprint route with URL like:
GET /api/survey?broadcast=false&organization=2
It was taking over 30 seconds to return the result. I have both the columns indexed in postgres and also there is a composite index using both these columns also. Also, when I run the query in postgres, it returns the result in milliseconds. So, I was confused as to why is it taking so long via blueprint route.
So, I modified the route by overriding it in the controller:
find: function (req, res){
var packet = req.params.all();
var myQuery = "select * from survey where 1=1 ";
Object.keys(packet).forEach(function (key){
myQuery += " and "+ key + " = " + packet[key] + " "
})
Survey.query(myQuery, function (err, result){
if(err){
return res.json(500, err);
}
else{
return res.json(200, result.rows)
}
})
},
and then I could get extremely fast performance this way. So my question is, should I avoid using Waterline's methods whenever performance is a requirement or is there anything I did wrong in my model definition or anywhere else ?
