Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query planner can't see through WITH alises to indexable properties #12356

Open
Pablissimo opened this issue Dec 9, 2019 · 2 comments
Open

Query planner can't see through WITH alises to indexable properties #12356

Pablissimo opened this issue Dec 9, 2019 · 2 comments
Assignees

Comments

@Pablissimo
Copy link

@Pablissimo Pablissimo commented Dec 9, 2019

It seems the Neo4j query planner can't 'see through' WITH statements where a variable is renamed in the same way it can if the variable name remains the same. This isn't a super likely query, but it's easily doable during refactoring and not user-obvious why the performance profile of the two queries would be radically different.

Given a sample graph:

CREATE CONSTRAINT ON (n: Node) ASSERT n.id IS UNIQUE

FOREACH (r in range(1, 1000000) | MERGE (n: Node { id: r }))
RETURN 1

While the following query with a redundant WITH, while unusual, is efficient (NodeUniqueIndexSeek, 2 db hits):

MATCH (n: Node)
WITH n
WHERE n.id = 123456
RETURN n

image

Aliasing the variable in the WITH causes a NodeByLabelScan and 2,000,001 hits:

MATCH (n: Node)
WITH n as nAlias
WHERE nAlias.id = 123456
RETURN nAlias

image

It's as if the optimiser's forgotten/doesn't realise that nAlias = n for the purposes of the WHERE clause (or elides the redundant WITH away entirely?), and isn't pushing the predicate into the original MATCH.

Expected behaviour

Aliasing in the WITH statement still causes the WHERE to be evaluated against the extant unique index on the filtered property.

Actual behaviour

The optimiser doesn't consider the unique index and does a NodeByLabel scan instead.

Setup

  • Neo4j Desktop, running Enterprise 3.5.13
  • Using the Neo4j Desktop browser
  • 8GB min heap, 16GB max heap
  • Windows x64
@Hunterness

This comment has been minimized.

Copy link
Contributor

@Hunterness Hunterness commented Dec 9, 2019

Hi, I agree that this seems wrong so thanks for reporting. We will look into it.

/Therese, Team Cypher

@Hunterness Hunterness self-assigned this Dec 9, 2019
@Lojjs Lojjs added performance and removed bug labels Dec 10, 2019
@Lojjs

This comment has been minimized.

Copy link
Contributor

@Lojjs Lojjs commented Dec 10, 2019

This is not strictly speaking a bug since you get the correct result for your query, but I agree that the planner could be smarter about this. It is not a quick fix, but we have added it to our backlog of performance improvements.
Best regards Louise, Neo4j Cypher team

@sherfert sherfert removed the 3.5 label Dec 16, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.