So this recursive query, it takes 2 to 8 seconds, did I write it wrong?Or what can I do to make the query faster, how can I index it？

Users Dgraph
, ,
#1

So this recursive query, it takes 2 to 8 seconds, did I write it wrong?Or what can I do to make the query faster, how can I index it？

Tablets (14)
ugcId1.7GB
ugcUid1.2GB
rootUgcId1.1GB
forwardFrom1.0GB
forwardCount0.9GB
arith_name882.3MB
createTime826.5MB
isRoot651.5MB
isDeleted648.4MB
type607.7MB

query list(){
  var(func:eq(ugcId, 28286941376)) @recurse(depth:100000, loop: false){
    parentIds as ~forwardFrom
  }
  count(func:uid(parentIds)) @filter(lt(createTime,9223372036854775807 ) and eq(isDeleted,0)){
    count(uid)
  }
}

{
  "data": {
    "count": [
      {
        "count": 0
      }
    ]
  },
  "extensions": {
    "server_latency": {
      "parsing_ns": 140776,
      "processing_ns": 2214921716,
      "encoding_ns": 77079,
      "assign_timestamp_ns": 671889,
      "total_ns": 2215862476
    },
    "txn": {
      "start_ts": 179135227
    },
    "metrics": {
      "num_uids": {
        "": 0,
        "_total": 0,
        "createTime": 0,
        "isDeleted": 0,
        "ugcId": 0,
        "~forwardFrom": 0
      }
    }
  }
}
#2

Maybe you can try this instead. In my previous tests, I found that the order of filters has a great impact on performance.

query list(){
  var(func:eq(ugcId, 28286941376)) @recurse(depth:100000, loop: false){
    parentIds as ~forwardFrom
  }
  count(func:uid(parentIds)) @filter(eq(isDeleted,0) and lt(createTime,9223372036854775807 )){
    count(uid)
  }
}
#3

First of all, thank you
I tried it, but it didn’t improve much

{
  "data": {
    "count": [
      {
        "count": 0
      }
    ]
  },
  "extensions": {
    "server_latency": {
      "parsing_ns": 155957,
      "processing_ns": 2190177228,
      "encoding_ns": 75937,
      "assign_timestamp_ns": 681611,
      "total_ns": 2191157002
    },
    "txn": {
      "start_ts": 179135284
    },
    "metrics": {
      "num_uids": {
        "": 0,
        "_total": 0,
        "createTime": 0,
        "isDeleted": 0,
        "ugcId": 0,
        "~forwardFrom": 0
      }
    }
  }
}
#4

You mentioned that filters affect performance. It seems to be true. I removed the filter, and the performance is much faster.Once you add filters it’s very slow.But without filters I don’t get what I want, right?What should I do?

query list(){
  var(func:eq(ugcId, 28286941376)) @recurse(depth:100000, loop: true){
    parentIds as ~forwardFrom
  }
  count(func:uid(parentIds)){
    count(uid)
  }
}

{
  "data": {
    "count": [
      {
        "count": 0
      }
    ]
  },
  "extensions": {
    "server_latency": {
      "parsing_ns": 135726,
      "processing_ns": 1506403,
      "encoding_ns": 56098,
      "assign_timestamp_ns": 708113,
      "total_ns": 2481809
    },
    "txn": {
      "start_ts": 179135307
    },
    "metrics": {
      "num_uids": {
        "": 0,
        "_total": 0,
        "ugcId": 0,
        "~forwardFrom": 0
      }
    }
  }
}
#5

Maybe you can try this

query list(){
  var(func:eq(ugcId, 28286941376)) @recurse(depth:100000, loop: false){
    parentIds as ~forwardFrom
  }
  var(func:uid(parentIds)) @filter(eq(isDeleted,0)){
    parentIdsValid as uid
  }
  count(func:uid(parentIdsValid)) @filter(lt(createTime,9223372036854775807 )){
    count(uid)
  }
}
#7 
{
  "data": {
    "count": [
      {
        "count": 0
      }
    ]
  },
  "extensions": {
    "server_latency": {
      "parsing_ns": 230246,
      "processing_ns": 2626962532,
      "encoding_ns": 87335,
      "assign_timestamp_ns": 713767,
      "total_ns": 2628091540
    },
    "txn": {
      "start_ts": 179135350
    },
    "metrics": {
      "num_uids": {
        "": 0,
        "_total": 0,
        "createTime": 0,
        "isDeleted": 0,
        "ugcId": 0,
        "uid": 0,
        "~forwardFrom": 0
      }
    }
  }
}
#8

You can try to change isDeleted to bool type, and then use has() and not has() to determine whether the node is deleted. I have done similar things before, which is faster than eq().

But it is more likely that the lt() function takes up too much time, and the non equivalent query is much slower than eq(). You should try to remove the lt() condition first.

#9

I tried it, but it didn’t improve much

query list(){
  var(func:eq(ugcId, 28287204672)) @recurse(depth:100000, loop: false){
    parentIds as ~forwardFrom
  }
  count(func:uid(parentIds)) @filter(eq(isDeleted,0)){
    count(uid)
  }
}

{
  "data": {
    "count": [
      {
        "count": 0
      }
    ]
  },
  "extensions": {
    "server_latency": {
      "parsing_ns": 172789,
      "processing_ns": 2327115735,
      "encoding_ns": 113476,
      "assign_timestamp_ns": 783148,
      "total_ns": 2328254520
    },
    "txn": {
      "start_ts": 179135372
    },
    "metrics": {
      "num_uids": {
        "": 0,
        "_total": 1,
        "isDeleted": 0,
        "ugcId": 0,
        "~forwardFrom": 1
      }
    }
  }
}