apache / apache/age

how to not generate subplan instead anti join

Open
#2,200 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C
Stars
4.8k
Forks
523
Avg merge
1d 2h
Merged PRs (30d)
9

Description

**Describe the bug**
if we use not exists to find a path,the age will pass the query that cannot optimize to use anti join. So, when the table is large, the speed is so low.

**How are you accessing AGE (Command line, driver, etc.)?**
command line.

**What data setup do we need to do?**
```
EXPLAIN (costs off)
SELECT * FROM cypher('ldbc',$$
MATCH
(tag:Tag {name: "Slovenia"})<-[:HAS_TAG]-(message:Message),(message)<-[:REPLY_OF]-(comment:Comment)
WHERE NOT EXISTS((comment)-[:HAS_TAG]->(tag))
RETURN
tag.name
ORDER BY
tag.name ASC
LIMIT 100
$$) AS (name agtype);
```

**What is the necessary configuration info needed?**
the query plan as below:
Limit
-> Sort
Sort Key: (agtype_access_operator(VARIADIC ARRAY[ag_catalog._agtype_build_vertex(tag.id, _label_names('167798'::oid, tag.id), tag.properties), '"name"'::agtype]))
-> Hash Join
Hash Cond: (_age_default_alias_1.start_id = comment_1.id)
Join Filter: (NOT ((SubPlan 1))::agtype)
-> Hash Join
Hash Cond: ((age_id(ag_catalog._agtype_build_vertex(message_1.id, _label_names('167798'::oid, message_1.id), message_1.properties)))::graphid = _age_d
efault_alias_1.end_id)
-> Hash Join
Hash Cond: (message_1.id = _age_default_alias_0.start_id)
-> Append
-> Seq Scan on "Message" message_1
-> Seq Scan on "_agr_Comment-Message" message_2
-> Seq Scan on "_agr_Message-Post" message_3
-> Hash
-> Hash Join
Hash Cond: (_age_default_alias_0.end_id = tag.id)
-> Seq Scan on "HAS_TAG" _age_default_alias_0
-> Hash
-> Seq Scan on "Tag" tag
Filter: (properties @> '{"name": "Slovenia"}'::agtype)
-> Hash
-> Seq Scan on "REPLY_OF" _age_default_alias_1
-> Hash
-> Append
-> Seq Scan on "Comment" comment_1
-> Seq Scan on "_agr_Comment-Message" comment_2
SubPlan 1
-> Seq Scan on "HAS_TAG" _age_default_alias_2
Filter: ((start_id = comment_1.id) AND (end_id = (age_id(ag_catalog._agtype_build_vertex(tag.id, _label_names('167798'::oid, tag.id), tag.properties
)))::graphid))

**What is the command that caused the error?**
the type convert

**Expected behavior**
Limit
-> Sort
Sort Key: (agtype_access_operator(VARIADIC ARRAY[ag_catalog._agtype_build_vertex(tag.id, _label_names('167798'::oid, tag.id), tag.properties), '"name"'::agtype]))
-> Hash Join
Hash Cond: (message_1.id = _age_default_alias_0.start_id)
-> Append
-> Seq Scan on "Message" message_1
-> Seq Scan on "_agr_Comment-Message" message_2
-> Seq Scan on "_agr_Message-Post" message_3
-> Hash
-> Hash Left Join
Hash Cond: ((age_id(ag_catalog._agtype_build_vertex(comment_1.id, _label_names('167798'::oid, comment_1.id), comment_1.properties)))::graphid =
_age_default_alias_2.start_id)
-> Hash Join
Hash Cond: (comment_1.id = _age_default_alias_1.start_id)
-> Append
-> Seq Scan on "Comment" comment_1
-> Seq Scan on "_agr_Comment-Message" comment_2
-> Hash
-> Hash Join
Hash Cond: (_age_default_alias_1.end_id = _age_default_alias_0.start_id)
-> Seq Scan on "REPLY_OF" _age_default_alias_1
-> Hash
-> Hash Join
Hash Cond: (_age_default_alias_0.end_id = tag.id)
-> Seq Scan on "HAS_TAG" _age_default_alias_0
-> Hash
-> Seq Scan on "Tag" tag
Filter: (properties @> '{"name": "Slovenia"}'::agtype)
-> Hash
-> Hash Join
Hash Cond: (_age_default_alias_2.end_id = tag2.id)
-> Seq Scan on "HAS_TAG" _age_default_alias_2
-> Hash
-> Seq Scan on "Tag" tag2
Filter: (ag_catalog._agtype_build_vertex(id, _label_names('167798'::oid, id), properties) IS NULL)

**Environment (please complete the following information):**
- Version: [e.g. 0.4.0]

**Additional context**
Add any other context about the problem here.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.