When querying only for `totalCount` within a `*Connection` query, the generated cypher queries and returns too much data resulting in serious performance-issues

[andreloeffelmann]

Describe the bug
When querying only for totalCount within a *Connection query, database-internal data is returned for each found node-instance resulting in a HUGE response. This totally kills the performance when having many nodes.
See the steps to reproduce. When running this query on our production database with Neo4j Desktop, the app completely dies and shuts down. And we only have around 2m nodes of this type. We have other types with ~80m nodes. Don't want to know what would happen if I executed this query on that type...

Type definitions

type Norm {
  code: String!
}

To Reproduce

1. Run a server with the type defs
2. Execute the following Query:

query {
  normsConnection {
    totalCount
  }
}

3. The following cypher is generated

MATCH (this0:Norm)
WITH collect({ node: this0 }) AS edges
WITH edges, size(edges) AS totalCount
CALL {
  WITH edges
  UNWIND edges AS edge
  WITH edge.node AS this0
  RETURN collect({ node: { __id: id(this0), __resolveType: "Norm" } }) AS var1
}
RETURN { edges: var1, totalCount: totalCount } AS this

4. This results in a response with the following structure:

{
  "edges": [
    {
      "node": {
        "__resolveType": "Norm",
        "__id": 42942
      }
    },
    {
      "node": {
        "__resolveType": "Norm",
        "__id": 47617
      }
    },
  ....
  ],
  "totalCount": 185
}

5. The sub-object node with the keys __resolveType and __id is returned for EACH found instance.

Expected behavior
When querying on a connection including only totalCount, the irrelevant meta-information should NOT be returned to keep perfomance.
Even to query for is a perfomance killer, I think.
A simple cypher to aim for the count-store only should be sufficient:

MATCH (this0:Norm)
RETURN {totalCount:count(this0)}

System

• Version: @neo4j/graphql@6.5.0

[angrykoala]

Hi @andreloeffelmann

totalCount is generally intended to be used as part of a paginated result. For the case you mention, try using Aggregation Operations which should not return the edges, unless requested. Another alternative is to use pagination to return less edges (totalCount will still be the total count of edges)

This is still a valid performance improvement that should be addressed

[andreloeffelmann]

Hi @angrykoala,

according to Aggregation Operations I have just tried the following:

normsConnection {
    aggregate {
      count {
        nodes
      }
    }
}

This results in a cypher query even worse:

CALL {
    MATCH (this:Norm)
    RETURN { nodes: count(DISTINCT this) } AS var0
}
CALL {
    WITH *
    MATCH (this1:Norm)
    WITH collect({ node: this1 }) AS edges
    WITH edges, size(edges) AS totalCount
    CALL {
        WITH edges
        UNWIND edges AS edge
        WITH edge.node AS this1
        RETURN collect({ node: { __id: id(this1), __resolveType: "Norm" } }) AS var2
    }
    RETURN var2, totalCount
}
RETURN { edges: var2, totalCount: totalCount, aggregate: { count: var0 } } AS this

Now in addition to the aggregate, the totalCount and the edges are queried, resulting in this response:

{
  "aggregate": {
    "count": {
      "nodes": 276
    }
  },
  "edges": [
    {
      "node": {
        "__resolveType": "Norm",
        "__id": 466073
      }
    },
    {
      "node": {
        "__resolveType": "Norm",
        "__id": 466074
      }
    },
....
  ],
  "totalCount": 276
}

[angrykoala]

Hi @andreloeffelmann
Sorry, my bad, this last query has a bug that causes that extra edges to be returned. Until that is fixed you can use the deprecated aggregate queries (I've just confirmed it doesn't return the edges) in version 6:

normsAggregate {
      count {
        nodes
    }
}

This one is deprecated and will be replaced by the query you tried in version 7 once we have fixed this issue.

Thanks for your patience on this one

[angrykoala]

This issue is now fixed for the queries such as:

normsConnection {
    aggregate {
      count {
        nodes
      }
    }
}

It will be available on the next 6.x release, I'll reopen this issue as it is still not solved for totalCount

[andreloeffelmann]

Hi @angrykoala , thanks for the fast fix! I can confirm that counting with aggregate within a connection query now performs well :) As soon as totalCount is also considered, this can be closed I guess

[andreloeffelmann]

Hi @angrykoala due to #6206 we cannot use

aggregate {
      count {
        nodes
      }
}

on all our types. But totalCount is not working either because we face the perfomance issues reported above.
So... can you fix either this or #6206?
brgds

[angrykoala]

Hi @andreloeffelmann

Would it be possible to apply the workaround in #6206 ?

The underlying cause of #6206 is a variable name conflict, which may affect other queries, not only aggregations, so just fixing this one will likely not be enough long term

I'll discuss #6206 with the team to see if we can find a fix for these naming conflicts