panosfol commented on issue #983:
URL: https://github.com/apache/age/issues/983#issuecomment-1624043467
**Multiple relationships**
So I implemented my solution with some basic-not optimal changes, just to
make it work and correctly produce a query plan.
There query that I tested my solution on is :
```
MATCH (charlie {name: 'Charlie
Sheen'})-[:ACTED_IN]->(movie)<-[:DIRECTED]-(director)
RETURN movie.title, director.name
```
I used `ALTER TABLE` command to add a new columnd to the `_ag_label_vertex`
and the `_ag_label_edge` tables named (for now) `new_id`. This new column holds
the `label_id` after the application of the bit shift mask. After that I
changed the following things in the codebase :
- changed the argument at the `scanNSItemForColumn` function inside the
`make_edge_expr` and `make_vertex_expr`
to `new_id` instead of `id`
- changed the `_graphid` function to return the `entry_id` without combining
it with the `label_id`
- changed the `create_edge` and `create_vertex` functions to assign the
`label_id` in the new column.
Some of the above changes were made with a not so optimal way in sake of
implementing the solution. In case of actual implementation the changes will be
correctly calculated and applied.
Below are the 2 query plans, both in PG13 version. The first one is the
current state and the second one is afer the changes I made to the codebase:
```
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=164.97..330.44 rows=2184 width=64)
Hash Cond: (movie.id = _age_default_alias_0.end_id)
-> Append (cost=0.00..52.11 rows=2141 width=40)
-> Seq Scan on _ag_label_vertex movie_1 (cost=0.00..0.00 rows=1
width=40)
-> Seq Scan on "Person" movie_2 (cost=0.00..20.70 rows=1070
width=40)
-> Seq Scan on "Movie" movie_3 (cost=0.00..20.70 rows=1070
width=40)
-> Hash (cost=162.44..162.44 rows=203 width=56)
-> Hash Join (cost=97.59..162.44 rows=203 width=56)
Hash Cond: (director.id = _age_default_alias_1.start_id)
-> Append (cost=0.00..52.11 rows=2141 width=40)
-> Seq Scan on _ag_label_vertex director_1
(cost=0.00..0.00 rows=1 width=40)
-> Seq Scan on "Person" director_2 (cost=0.00..20.70
rows=1070 width=40)
-> Seq Scan on "Movie" director_3 (cost=0.00..20.70
rows=1070 width=40)
-> Hash (cost=97.36..97.36 rows=19 width=24)
-> Hash Join (cost=74.55..97.36 rows=19 width=24)
Hash Cond: (_age_default_alias_1.end_id =
_age_default_alias_0.end_id)
Join Filter:
_ag_enforce_edge_uniqueness(_age_default_alias_0.id, _age_default_alias_1.id)
-> Seq Scan on "DIRECTED" _age_default_alias_1
(cost=0.00..18.80 rows=880 width=24)
-> Hash (cost=74.38..74.38 rows=13 width=16)
-> Hash Join (cost=52.15..74.38 rows=13
width=16)
Hash Cond:
(_age_default_alias_0.start_id = charlie.id)
-> Seq Scan on "ACTED_IN"
_age_default_alias_0 (cost=0.00..18.80 rows=880 width=24)
-> Hash (cost=52.12..52.12 rows=3
width=8)
-> Append (cost=0.00..52.12
rows=3 width=8)
-> Seq Scan on
_ag_label_vertex charlie_1 (cost=0.00..0.00 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
-> Seq Scan on "Person"
charlie_2 (cost=0.00..26.05 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
-> Seq Scan on "Movie"
charlie_3 (cost=0.00..26.05 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
(30 rows)
```
```
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=164.97..330.44 rows=2184 width=64)
Hash Cond: (movie.id = _age_default_alias_0.end_id)
-> Append (cost=0.00..52.11 rows=2141 width=48)
-> Seq Scan on _ag_label_vertex movie_1 (cost=0.00..0.00 rows=1
width=48)
-> Seq Scan on "Person" movie_2 (cost=0.00..20.70 rows=1070
width=48)
-> Seq Scan on "Movie" movie_3 (cost=0.00..20.70 rows=1070
width=48)
-> Hash (cost=162.44..162.44 rows=203 width=56)
-> Hash Join (cost=97.59..162.44 rows=203 width=56)
Hash Cond: (director.id = _age_default_alias_1.start_id)
-> Append (cost=0.00..52.11 rows=2141 width=48)
-> Seq Scan on _ag_label_vertex director_1
(cost=0.00..0.00 rows=1 width=48)
-> Seq Scan on "Person" director_2 (cost=0.00..20.70
rows=1070 width=48)
-> Seq Scan on "Movie" director_3 (cost=0.00..20.70
rows=1070 width=48)
-> Hash (cost=97.36..97.36 rows=19 width=24)
-> Hash Join (cost=74.55..97.36 rows=19 width=24)
Hash Cond: (_age_default_alias_1.end_id =
_age_default_alias_0.end_id)
Join Filter:
_ag_enforce_edge_uniqueness(_age_default_alias_0.id, _age_default_alias_1.id)
-> Seq Scan on "DIRECTED" _age_default_alias_1
(cost=0.00..18.80 rows=880 width=24)
-> Hash (cost=74.38..74.38 rows=13 width=16)
-> Hash Join (cost=52.15..74.38 rows=13
width=16)
Hash Cond:
(_age_default_alias_0.start_id = charlie.id)
-> Seq Scan on "ACTED_IN"
_age_default_alias_0 (cost=0.00..18.80 rows=880 width=24)
-> Hash (cost=52.12..52.12 rows=3
width=8)
-> Append (cost=0.00..52.12
rows=3 width=8)
-> Seq Scan on
_ag_label_vertex charlie_1 (cost=0.00..0.00 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
-> Seq Scan on "Person"
charlie_2 (cost=0.00..26.05 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
-> Seq Scan on "Movie"
charlie_3 (cost=0.00..26.05 rows=1 width=8)
Filter: (properties
@> agtype_build_map('name'::text, '"Charlie Sheen"'::agtype))
(30 rows)
```
Those 2 query plans were generated from the 2 graphs that hold exactly the
same info. Im planning on adding more data to see how the query plan changes
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
