AnnXie2001 opened a new issue, #2200:
URL: https://github.com/apache/age/issues/2200
**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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
