How to speed up ORDER BY

Neo4j Graph Platform Cypher
1

Hello, I have a question about the ORDER BY performance of neo4j.

  • neo4j version: 4.0.6 / neo4j desktop

Node & Relationship

Node - 10,001,203 EA

  1. Article - 10,000,000 EA
  2. ...

Releationship - 11,251,324 EA

  1. Section-[CONSISTS_OF]->Article

Indexes (native-btree-1.0)

| 1 | "article_index" | ["Article"] | ["articleId", "officeId"] | "native-btree-1.0" |
| 2 | "office_index" | ["Article"] | ["officeId"] | "native-btree-1.0" |
| 3 | "order_index" | ["Article"] | ["updateDateTime"] | "native-btree-1.0" |

without order by desc

MATCH (a:Article {officeId:'001'})<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'}) RETURN a LIMIT 100

image
image932×380 41.6 KB

with order by desc

MATCH (a:Article {officeId:'001'})<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'}) RETURN a ORDER BY a.updateDateTime DESC LIMIT 100

image
image1148×412 22.2 KB

The number of 'Article' nodes is about 10 million.
and The number of '(a:Article {officeId: '001'})' is 227,245.

There are many performance differences depending on whether ORDER BY is used or not.

Is there any other way to address ORDER BY performance issues?

Thanks

2

Can you try this?

MATCH (a:Article {officeId:'001'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
MATCH (a)<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'})
RETURN a
3

@soham.dhodapkar

MATCH (a:Article {officeId:'001'})
WITH a
ORDER BY a.updateDateTime DESC
RETURN a LIMIT 100

The above query itself is slow. it takes about 400ms

Is there a problem with the 'updateDateTime' index?
Or need something like a descending index?

The 'Article' Node properties are as follows.

        "properties": {
          "contents.title": "...",
          "contents.content": "...",
          "officeId": "001",
          "articleId": "0009656943",
          "contents.section": "10464f000",
          "insertDateTime": "20180820035121",
          "updateDateTime": "20181029013007",
          "refinedContent": "..."
        }
4

Do you have indexes on officeId and updateDateTime ?
Also, can you please profile the query and share the query execution plan?

PROFILE
MATCH (a:Article {officeId:'001'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
MATCH (a)<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'})
RETURN a
5

Indexes

image
image1293×504 29.4 KB

I have indexes on officeId and updateDateTime.

Profiles

PROFILE
MATCH (a:Article {officeId:'001'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
MATCH (a)<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'})
RETURN a

image
image1132×415 21.1 KB

6

And are the results returned correct?
I am trying to think of a way to make this faster than 282ms. Number of (a:Article {officeId: '001'}) is 227,245. How many nodes are (s:Section {sectionId:'104'}) ?

7

We can try to leverage index-backed ordering, but this requires a means to hint the type of the property, such as in a WHERE clause against a value of the same type. Can you give this a try?

PROFILE
MATCH (a:Article {officeId:'001'})
where a.updateDateTime < dateTime() AND (a)<-[:CONSISTS_OF]-(:Section {sectionId:'104'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
RETURN a
8

@soham.dhodapkar

MATCH (a:Article {officeId:'001'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
MATCH (a)<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'})
RETURN a

The result of returned is not correct.There are only seven results.

MATCH (a)<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'})

I think the above query is executed as a result from 'ORDER BY a.updateDateTime DESC LIMIT 100'.

image
image1208×820 39.9 KB

And Number of (s:Section {sectionId:'104'}) is only one node

9

@andrew_bowman

PROFILE
MATCH (a:Article {officeId:'001'})
where a.updateDateTime < dateTime() AND (a)<-[:CONSISTS_OF]-(:Section {sectionId:'104'})
WITH a
ORDER BY a.updateDateTime DESC
LIMIT 100
RETURN a

No result from the above query. I think where a.updateDateTime < dateTime() clause doesn't seem to work.

updateDateTime format

"updateDateTime": "20181029013007"

Profile

image
image3714×1034 231 KB

Is this right condition between updateDateTime and dateTime()?

10

Ah, I thought you were using a dateTime type. If you're using the epoch time, then maybe something like WHERE a.updateDateTime < timestamp() for that segment.

11

I just changed the property 'insertDateTime' to dateTime type with the query below

CALL apoc.periodic.iterate(
    "MATCH (a:Article) RETURN a",
    "SET a.insertDateTime = datetime({epochmillis: apoc.date.parse(a.insertDateTime, 'ms', 'yyyyMMddhhmmss')})",
    {batchSize:1000, parallel:true})

I made a Index on 'insertDateTime' property and I ran the command you recommend

PROFILE
MATCH (a:Article {officeId:'001'})
where a.insertDateTime < datetime() AND (a)<-[:CONSISTS_OF]-(:Section {sectionId:'104'})
WITH a
ORDER BY a.insertDateTime DESC
LIMIT 100
RETURN a

image
image3686×1038 250 KB

Below query is also slow ..

PROFILE MATCH (a:Article {officeId:'001'})<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'}) 
WHERE a.insertDateTime < dateTime()
RETURN a ORDER BY a.insertDateTime DESC LIMIT 100

image
image3782×894 202 KB

And this query is faster than both above

PROFILE MATCH (a:Article {officeId:'001'})<-[r:CONSISTS_OF]-(s:Section {sectionId:'104'}) 
RETURN a ORDER BY a.insertDateTime DESC LIMIT 100

image
image2268×826 46.8 KB

Is leverage index-backed ordering working?