🐛 [Bug] Sort by "most commented" or "most popular" waaaay to slow

[roschaefer]

🐛 Bugreport

On our production data (17887 posts, 112179 comments on 2019-08-15 21:15), ordering the posts in the newsfeed by "Most commented" or "Popular" is so slow that you don't get a result in the first 4 seconds.

Steps to reproduce the behavior

1. Run your app locally on production data (or on https://nitro-production.human-connection.org/ as soon it is rolled out)
2. Filter for "Most commented" or "Popular"
3. Wait and have a coffee ☕

Here is the graphql query + relevant variables:
orderBy: commentsCount_desc
orderBy: shoutedCount_desc

query Post($filter: _PostFilter, $first: Int, $offset: Int, $orderBy: [_PostOrdering]) {
  Post(filter: $filter, first: $first, offset: $offset, orderBy: $orderBy) {
    id
    title
    contentExcerpt
    createdAt
    disabled
    deleted
    slug
    image
    author {
      id
      avatar
      slug
      name
      disabled
      deleted
      contributionsCount
      shoutedCount
      commentsCount
      followedByCount
      followedByCurrentUser
      location {
        name: nameEN
      }
      badges {
        id
        icon
      }
    }
    commentsCount
    categories {
      id
      name
      icon
    }
    shoutedCount
  }
}

Expected behavior

Waiting time should not exceed 4 seconds.

Version & Environment

robert@e480 ~/D/h/Human-Connection> chromium --version
Chromium 76.0.3809.100 Arch Linux
robert@e480 ~/D/h/Human-Connection> cat /proc/version
Linux version 5.2.6-arch1-1-ARCH (builduser@heftig-3961) (gcc version 9.1.0 (GCC)) #1 SMP PREEMPT Sun Aug 4 14:58:49 UTC 2019

[roschaefer]

Related: https://neo4j.com/developer/kb/fast-counts-using-the-count-store/

Beware that we cannot ask for WHERE NOT something.deleted = true so I suggest to have a dedicated counter on the Post which we could use in _PostFilter. I think it's OK to order for the counts of all comments, including disabled or deleted comments.

[roschaefer]

Bad news

I did some research on this and opened neo4j-graphql/neo4j-graphql-js#303

I think this is a problem that needs to be fixed in neo4j-graphql-js. In the meantime we should probably remove the sort by "Most commented" and "Most popular" from the available options.

Neo4j count store

Although the neo4j count store is super fast, we cannot use it because:

Due to limitations of the query planner, the count store will only be leveraged if the count() aggregation is alone on a WITH or RETURN. If any other variable is in scope along with the count() aggregation, the count store will not be used.

From here

So the count store can only be used per label not per node.

Debugging the performance of generated queries

I would like to share my findings how to set params in the neo4j browser console: