Replies: 1 comment
-
There is another syntax that gets the same result in age. SELECT * -------------------------------- query plan------------------------------------------------------------------------------------------------------- -> Compared to agensgraph, the query plan seems to be created inefficiently.
|
Beta Was this translation helpful? Give feedback.
-
In the pattern query using the exists syntax,
I found the difference between the syntax used in agensgraph and age.
These are different queries that get the same result.
When executing example 1, query execution time is very slow in agensgraph.
This is especially true if the number of data is large.
However, in age, the syntax of example 1 is supported, and an error occurs in the syntax of example 2.
example 1)
-- normal case ( 'b' objects added in match)
SELECT *
FROM cypher('test_graph', $$
MATCH (n:Person{name: 'Andres2'}), (b)
WHERE exists((n)-[:acted_in]->(b))
RETURN n
$$) as (name agtype);
example 2)
-- error case using pattern with exists syntax in age
-- It is normal in agensgraph;
SELECT *
FROM cypher('test_graph', $$
MATCH (n:Person{name: 'Andres2'})
WHERE exists((n)-[:acted_in]->(b))
RETURN n
$$) as (name agtype);
ERROR: variable 'b' does not exist
LINE 4: WHERE exists((n)-[:acted_in]->(b))
Up to this point, it can be understood as the difference in the syntax of the two products,
but when you look at the query plan blow, there are points that need to be reviewed.
query plan at example 1 : syntax in age)
[expected issue]
While referring to the 'b' object in the match clause,
a seq scan may occur for all objects in the graph path, resulting in performance degradation.
-------------------------------- query plan ----------------------------------------------------------------------------------
Nested Loop (cost=0.00..176882.11 rows=10803 width=32)
Join Filter: (SubPlan 1)
-> Append (cost=0.00..66.00 rows=3601 width=8)
-> Seq Scan on ag_vertex b (cost=0.00..0.00 rows=1 width=8)
-> Seq Scan on address b_1 (cost=0.00..22.00 rows=1200 width=8)
-> Seq Scan on person b_2 (cost=0.00..22.00 rows=1200 width=8)
-> Seq Scan on movie b_3 (cost=0.00..22.00 rows=1200 width=8)
-> Materialize (cost=0.00..25.03 rows=6 width=46)
-> Seq Scan on person n (cost=0.00..25.00 rows=6 width=46)
Filter: (properties.'name'::text = '"Andres"'::jsonb)
SubPlan 1
-> Index Only Scan using acted_in_end_idx on acted_in "<0000000003>" (cost=0.15..8.17 rows=1 width=0)
Index Cond: (("end" = b.id) AND (start = n.id))
(13 rows)
example 2 : syntax in agensgraph)
The query plan of example 2 is more efficient and the execution time is searched faster.
Since the object 'b' is referenced in the exists statement, the query plan is created much more efficiently.
-------------------------------- query plan ----------------------------------------------------------------------------------
Seq Scan on person n (cost=0.00..5026.00 rows=3 width=32)
Filter: ((properties.'name'::text = '"Andres"'::jsonb) AND (SubPlan 1))
SubPlan 1
-> Index Only Scan using acted_in_start_idx on acted_in "<0000000004>" (cost=0.15..20.24 rows=5 width=0)
Index Cond: (start = n.id)
(5 rows)
my suggestions)
Beta Was this translation helpful? Give feedback.
All reactions