Query optimization to reduce query time and memory usage

I’ve found some queries that seem to really consume gigabytes of memory almost immediately. Basically I have a function that loops over a list of projects and runs a query for each one of those projects (below). One list has 150 projects so this query is running 150 times with the project and region UIDs related to said project. After the end of the loop, I see in docker stats that memory usage is at 17 - 18gb, up from 4gb. It never goes back down until I restart dgraph. I’m wondering why this query in particular would cause this. Any help greatly appreciated!

{
  liUids as var(func: type(line_item)) @cascade {
    purchase_orders @cascade {
      projects @filter(uid(0xf78dd4))
      regions @filter(uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d))
    }
  }

  var(func: uid(liUids)) {
    li_uid_count as count(uid)
    line_item.amount_var as line_item.amount
    line_item.open_unbilled_amount_var as line_item.open_unbilled_amount
  }
  
  workflowUids as var(func: type(workflow)) @filter(not has(purchase_orders)) @cascade {
    projects @filter(uid(0xf78dd4))
    workflow_types @filter(eq(crimson_category.id, ["workflow_types:purchase_order_req"]))
    # workflow_states @filter(not eq(crimson_category.id, ["workflow_states:started", "workflow_states:waiting_on_manager_approval", "workflow_states:waiting_on_vp_approval", "workflow_states:waiting_on_peer_review"]))
    regions @filter(uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d))
  }

  var(func: uid(workflowUids)) {
    workflow_uid_count as count(uid)
    workflow.requisition_amount_var as workflow.requisition_amount
  }

  committed() {
    workflowCount: sum(val(workflow_uid_count))
    liCount: sum(val(li_uid_count))
    wAmt as workflowAmount: sum(val(workflow.requisition_amount_var))
    liAmount: sum(val(line_item.amount_var))
    liOpAmt as liOpenAmount: sum(val(line_item.open_unbilled_amount_var))
    committed: math(wAmt + liOpAmt)
  }
      
  lineItemsCommitted(func: uid(liUids)) @normalize {
    uid: uid
    committed: line_item.open_unbilled_amount
    purchase_orders {
      regions {
        region: region.short_name
        region_uid: uid
      }
    }
    crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
      category: crimson_category.name
      category_uid: uid
      category_type: crimson_category.type
      crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
        parent_category: crimson_category.name
        parent_category_uid: uid
      }
    }
  }
  
  projectSpendUids as var(func: type(project_spend)) @cascade {
    regions @filter(uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d))
    projects @filter(uid(0xf78dd4))
  }
  
  projectSpends(func: uid(projectSpendUids)) @normalize {
    uid: uid
    manual_spend as spend: project_spend.total
    regions {
      region: region.short_name
      region_uid: uid
    }
    crimson_categories @filter(eq(crimson_category.type, "project_spend_categories")) {
      category: crimson_category.name
      category_type: crimson_category.type
      category_uid: uid
    }
  }
  
  materialUids as var(func: type(inventory_item_transaction)) @cascade {
    regions @filter(uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d))
    projects @filter(uid(0xf78dd4))
  }
      
  materials(func: uid(materialUids)) @normalize {
    uid: uid
    materials_amount as materialsAmt: inventory_item_transaction.amount
    materials_tax as materialsTax: inventory_item_transaction.tax_amount
    regions {
      region: region.short_name
      region_uid: uid
    }
  }
  
  workflowsCommitted(func: uid(workflowUids)) @normalize {
    uid: uid
    committed: workflow.requisition_amount
    regions {
      region: region.short_name
      region_uid: uid
    }
    crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
      category: crimson_category.name
      category_type: crimson_category.type
      category_uid: uid
    }
  }

  q(func: uid(0xf78dd4)) @normalize {
    project_sub_types {
      sub_type_uid: uid
      sub_type: project_sub_type.name
    }
    ~projects @filter(type(project_budget)) {
      project_budget_uid: uid
      allocation: budget as project_budget.budget
      regions {
        region_uid: uid
        region: region.short_name
      }
      crimson_categories @filter(eq(crimson_category.type, ["workflow_categories", "project_spend_categories"])) {
        category_uid: uid
        category_type: crimson_category.type
        category: crimson_category.name
        crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
          parent_category: crimson_category.name
        }
      }
    }
  }
  
  invoice_uids as var(func: type(invoice)) @cascade {
    purchase_orders {
      regions @filter(uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d))
      projects @filter(uid(0xf78dd4))
    }
  }
  
  regions(func: uid(0xf4f5a8,0x1ab0e2f,0x1ab0e2e,0x1ab0e2b,0x1ab0e2c,0xf6f153,0xf5e00e,0x1ab0e2d,0xf67c36,0xf2f9db,0xf78d6d)) {
    uid
    region.name
    region.short_name
  }
    
  invoices(func: uid(invoice_uids)) @normalize {
    count(uid)
    uid
    spend: invoice_amount as invoice.amount
    purchase_orders {
      regions {
        region: region.short_name
      }
    }
    
    line_items {
      crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
        category: crimson_category.name
        crimson_categories @filter(eq(crimson_category.type, "workflow_categories")) {
          parent_category: crimson_category.name
        }
      }
    }
  }
    
  aggregates() {
    total_budget: sum(val(budget))
    spend: sum(val(invoice_amount))
    manualSpend: sum(val(manual_spend))
    materials: sum(val(materials_amount))
    materialsTax: sum(val(materials_tax))
  }
}

Here is a Jaeger trace of on of the queries:

query_trace.json (115.4 KB)

Hi @nodeworks,
Could you please share the prometheus metrics from the alpha (/debug/prometheus_metrics)?

Hi @anand

Thanks @nodeworks.
Could you please also share the go_memstats_heap_idle_bytes. It’s described in this link.