I am trying to make an aggregate complex query. But the query takes 4-5 seconds. Actually there is not much data(5k record) but i have to use 5 lookups.
My query is as follows;
db.getCollection("pairpercentages").explain('executionStats').aggregate([
{
$lookup: {
from: "filtereditems",
let: {
pair_src: "$pair",
buy_src: "$buy",
sell_src: "$sell",
percentage_src: "$percentage",
buycontractaddress_src: "$buycontractaddress",
sellcontractaddress_src: "$sellcontractaddress",
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$or: [
{
$eq: ["$$pair_src", "$pair"],
},
{
$eq: ["$pair", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$buy_src", "$buy"],
},
{
$eq: ["$buy", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$sell_src", "$sell"],
},
{
$eq: ["$sell", "ALL"],
},
],
},
},
{
$expr: {
$lt: ["$$percentage_src", "$percentage"],
},
},
{
$expr: {
$or: [
{
$eq: ["$contractaddress", ""]
},
{
$eq: ["$$buycontractaddress_src", "$contractaddress"],
},
{
$eq: ["$$sellcontractaddress_src", "$contractaddress"],
},
{
$eq: ["$contractaddress", "ALL"],
},
],
},
},
],
},
},
],
as: "filtered",
},
},
{
$match: {
filtered: {
$eq: [],
},
},
},
{
$lookup: {
from: "alarmitems",
let: {
pair_src: "$pair",
buy_src: "$buy",
sell_src: "$sell",
percentage_src: "$percentage",
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: ["$$pair_src", "$pair"],
},
{
$eq: ["$$buy_src", "$buy"],
},
{
$eq: ["$$sell_src", "$sell"],
},
],
},
},
},
],
as: "specialAlarmfilterCounter",
},
},
{
$addFields: {
specialAlarmFilterExists: {
$cond: {
if: { $gt: [{ $size: "$specialAlarmfilterCounter" }, 0] },
then: true,
else: false,
},
},
},
},
{
$lookup: {
from: "alarmitems",
let: {
pair_src: "$pair",
buy_src: "$buy",
sell_src: "$sell",
percentage_src: "$percentage",
specialAlarmFilterExists: "$specialAlarmFilterExists",
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: ["$$pair_src", "$pair"],
},
{
$eq: ["$$buy_src", "$buy"],
},
{
$eq: ["$$sell_src", "$sell"],
},
{
$gt: ["$$percentage_src", "$percentage"],
},
],
},
},
},
],
as: "specialAlarmfilter",
},
},
{
$addFields: {
specialAlarm: {
$cond: {
if: {
$and: [
{ $gt: [{ $size: "$specialAlarmfilter" }, 0] },
{ $eq: ["$specialAlarmFilterExists", true] },
],
},
then: 1,
else: 0,
},
},
},
},
{
$lookup: {
from: "alarmitems",
let: {
pair_src: "$pair",
buy_src: "$buy",
sell_src: "$sell",
percentage_src: "$percentage",
specialAlarmFilterExists: "$specialAlarmFilterExists",
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$or: [
{
$eq: ["$$specialAlarmFilterExists", false],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$pair_src", "$pair"],
},
{
$eq: ["$pair", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$buy_src", "$buy"],
},
{
$eq: ["$buy", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$sell_src", "$sell"],
},
{
$eq: ["$sell", "ALL"],
},
],
},
},
],
},
},
],
as: "alarmfilterCounter",
},
},
{
$addFields: {
allAlarmFilterExists: {
$cond: {
if: { $gt: [{ $size: "$alarmfilterCounter" }, 0] },
then: true,
else: false,
},
},
},
},
{
$lookup: {
from: "alarmitems",
let: {
pair_src: "$pair",
buy_src: "$buy",
sell_src: "$sell",
percentage_src: "$percentage",
specialAlarmFilterExists: "$specialAlarmFilterExists",
allAlarmFilterExists: "$allAlarmFilterExists",
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$or: [
{
$eq: ["$$specialAlarmFilterExists", false],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$pair_src", "$pair"],
},
{
$eq: ["$pair", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$buy_src", "$buy"],
},
{
$eq: ["$buy", "ALL"],
},
],
},
},
{
$expr: {
$or: [
{
$eq: ["$$sell_src", "$sell"],
},
{
$eq: ["$sell", "ALL"],
},
],
},
},
{
$expr: {
$gt: ["$$percentage_src", "$percentage"],
},
},
{
$expr: {
$eq: ["$$allAlarmFilterExists", true],
},
},
],
},
},
],
as: "alarmfilter",
},
},
{
$addFields: {
allAlarm: {
$cond: {
if: { $gt: [{ $size: "$alarmfilter" }, 0] },
then: 1,
else: 0,
},
},
},
},
{
$match: {
$or: [
{
$and: [
{ percentage: { $gt: 0 } },
{ specialAlarmFilterExists: { $eq: false } },
{ allAlarmFilterExists: { $eq: false } },
],
},
{ specialAlarm: { $gt: 0 } },
{ allAlarm: { $gt: 0 } },
],
},
},
{
$project: {
alarmfilter: 0,
filtered: 0,
specialAlarmfilter: 0,
specialAlarm: 0,
specialAlarmFilterExists: 0,
allAlarm: 0,
alarmfilter: 0,
allAlarmFilterExists: 0,
specialAlarmfilterCounter: 0,
alarmfilterCounter: 0,
updatedate: 0,
buydate: 0,
selldate: 0,
buycontractaddress: 0,
sellcontractaddress: 0,
buymultiple: 0,
sellmultiple: 0
},
},
{ $sort : { percentage : 1} }
])
I created indexes for the pairpercentages, filtered and alarm documents as follows.
db.pairpercentages.createIndex( { percentage: 1, updatedate: 1, pair: 1, buy: 1, sell: 1} );
db.pairpercentages.createIndex( { percentage: 1, pair: 1, buy: 1, sell: 1} );
db.pairpercentages.createIndex( { percentage: 1, updatedate: 1} );
db.filtereditems.createIndex( { buy: 1, sell: 1, pair: 1, user: 1, percentage: 1, contractaddress:1} );
db.alarmitems.createIndex( { buy: 1, sell: 1, pair: 1, user:1, percentage: 1, contractaddress:1} );
But when I look at the execution stats, I see that it takes too much time as follows.
“executionStats” : {
“executionSuccess” : true,
“nReturned” : 3298.0,
“executionTimeMillis” : 4432.0,
“totalKeysExamined” : 3298.0,
“totalDocsExamined” : 3298.0,
Where am i doing wrong? How can i solve it?