You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "rafsun42 (via GitHub)" <gi...@apache.org> on 2023/07/10 22:44:28 UTC
[GitHub] [age] rafsun42 opened a new issue, #1021: Research alternative of graphid
rafsun42 opened a new issue, #1021:
URL: https://github.com/apache/age/issues/1021
(no comment)
--
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-unsubscribe@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1664595430
@rafsun42 I have found a Postgres function that returns the Namespacename given the OID so one problem is solved. But i dont think its the right direction to just insert the tuple twice with no prep, the tuple holds crucial info about the table that is being insered to, its not as simple as just choosing to insert it in another table.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1659050354
@rafsun42 well a pretty obvious con is that we have to insert all the entries in the junction table so its going to double the time of inserting and updating. Other than that I don't see another issue.
The benefit is that it would work with changing the `get_label_name` function (and maybe it would work better with other changes).
Ill push a commit fixing the creation of the junction table and then Ill look into how we can insert the vertices in the junction table also
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] rafsun42 commented on issue #1021: Research alternative of graphid
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1652153892
@panosfol I am not sure if that's how postgres inheritance work. A row is not added to both child and parent table. Postgres has a documentation on it.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] rafsun42 commented on issue #1021: Research alternative of graphid
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1652158629
@panosfol Postgres' `FROM ONLY` clause may give you some hint.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1652159631
@rafsun42 ok thank you I will look into that!
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1664510476
@rafsun42 So there are 2 issues that im facing now. First there isn't a function in the cache that takes just the OID and returns the graph_name, only the other way around. I have for now hard coded the graph name that Im using so I can test things.
The other issue is that the server crashes when I try to insert the tuple in the junction table. I went to the `create_vertex` function in `cypher_create.c:476` and using the function `create_entity_result_rel_info()` I'm successfully creating the `ResultRelInfo` for the junction table. After that Im duplicating the `insert_entity_tuple` and Im using the same `elemTupleSlot` and the `ResultRelInfo` from the junction table. But the server crashes because the elemTupleSlot was created with 3 columns in mind and the junction table only has 2. I tried to copying the contents of the `node->elemTupleSlot` to a new variable but I get segmentation fault.
I will look into it more but it seems its a bit more complicated than I thought. Ill try to have it ready by tomorrow.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1648666558
I was searching the `get_label_name()` function and this idea of a junction table also works there. I would need to modify the function in 2 ways:
- instead of the `graphid` as argument it would take the `entry_id`
- use the `entry_id` to search the junction table and find the `label_id` that the vertex belongs to
After that the function will continue its flow as normal since the only difference is how we obtain the `label_id`.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1652155459
@rafsun42 According to the documentation it doesn't and I got a bit confused there. I can't pinpoint exactly how in the code the vertex is inserted in bot the label_table and the `ag_label_vertex` at the same time.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1652134864
Im in the process of a creating a junction table to hold just the `id` and the `label_id` of a given vertex. The table is created at the creation of the graph under the `<graph_name>` schema.
I have successfully created the table with its columns but from what I understand every vertex that is inserted in the graph should also be inserted in the junction table, and Im having a little trouble with the inheritance system.
If I understand it correctly the inheritance system is the reason that when a vertex is added to a label table, its also added to the `ag_label_vertex` table.
Therefore I believe that the right course of action is for the junction table to have the `ag_label_vertex` table as a parent and then to alter the table through the postgres API to remove the unnecessary columns.
The problem with that is that I dont have access to the `ag_label_vertex` table at the creation of the graph, so I can pass it as a list for the `parents` argument to the function that creates the junction table. Is this the correct way to tackle this? How should i proceed?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] rafsun42 commented on issue #1021: Research alternative of graphid
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1659046326
@panosfol
The [documentation](https://www.postgresql.org/docs/current/ddl-inherit.html) I was referring to before states:
> Inheritance does not automatically propagate data from INSERT or COPY commands to other tables in the inheritance hierarchy.
That is the reason I was concerned if inheritance would work well or not.
So, we have to do this without using inheritance. Create the junction table independently with foreign key constraint on both columns. Any pros and cons of not using inheritance?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1658924876
@rafsun42 So right now i have successfully created the `junction_table` as a parent to `_ag_label_vertex`, and im in the process of creating the function that will scan the table and return the `label_id` of the given vertex. The function though has a problem.
When I do a scan the `junction_table`, it is basically empty as far as the scan is concerned because it doesn't search the child tables (I ve noticed this behaviour with a debugger in the `get_vertex` function).
Should we insert every vertex in the `junction_table` also? Or is there another way we move forward?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] panosfol commented on issue #1021: Research alternative of graphid
Posted by "panosfol (via GitHub)" <gi...@apache.org>.
panosfol commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1664663801
@rafsun42 Ok so on further inspection the match clause also wont work without some changes because the patter is different if i create a new vertex for the junction table. I need to know how to move forward. Should we create the new vertex and make the necessary changes or try to just insert the tuple at the execution stage? Inserting the tuple by itself is complicated and Im not even sure if its doable to be honest
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] rafsun42 commented on issue #1021: Research alternative of graphid
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1021:
URL: https://github.com/apache/age/issues/1021#issuecomment-1634660959
## Approach 1 - Using a trimmed and indexed version of label table for join
### The query
Cypher query that extracts label ID (see the QPT just below):
```sql
SELECT * FROM cypher('imdb',
$$
EXPLAIN MATCH (p:Person{primaryName:'Christian Bale'})-[e:IS_IN]->(:Title) RETURN e
$$) as (a agtype);
```
```txt
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Gather (cost=457881.41..742804.15 rows=105 width=32)
Workers Planned: 2
-> Parallel Hash Join (cost=456881.41..741793.65 rows=44 width=32)
Hash Cond: (e.start_id = p.id)
-> Parallel Seq Scan on "IS_IN" e (cost=0.00..284748.30 rows=43610 width=29)
Filter: ((_extract_label_id(end_id))::integer = 3)
-> Parallel Hash (cost=456815.59..456815.59 rows=5266 width=8)
-> Parallel Seq Scan on "Person" p (cost=0.00..456815.59 rows=5266 width=8)
Filter: (properties @> agtype_build_map('primaryName'::text, '"Christian Bale"'::agtype))
(9 rows)
```
My goal is to replace the use of `_extract_label_id` in the Filter node. This line is filtering out edges where end node is not a `Title`.
### Building the solution that does not extract label ID
My solution is to create a trimmed and indexed table of Title table. Calling it `Title_hash`. It has only ID column and indexed by hash method.
```sql
CREATE SCHEMA imdb_dev;
CREATE TABLE imdb_dev."Title_hash" (id graphid);
CREATE INDEX imdb_dev_title_hash_id ON imdb_dev."Title_hash" USING hash (id);
INSERT INTO imdb_dev."Title_hash" SELECT id FROM imdb."Title"; -- load the new table
```
### Query on the new solution
The SQL query that uses the new table. It is equivalent to the above cypher query.
```sql
EXPLAIN
SELECT *
FROM imdb."IS_IN" e
JOIN imdb."Person" p
ON e.start_id = p.id
WHERE
(p.properties @> agtype_build_map('primaryName'::text, '"Christian Bale"'::agtype))
AND
e.end_id IN (SELECT id FROM imdb_dev."Title_hash")
;
```
```txt
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Gather (cost=457881.41..745430.91 rows=20933 width=238)
Workers Planned: 2
-> Nested Loop Semi Join (cost=456881.41..742337.61 rows=8722 width=238)
-> Parallel Hash Join (cost=456881.41..730763.53 rows=8722 width=238)
Hash Cond: (e.start_id = p.id)
-> Parallel Seq Scan on "IS_IN" e (cost=0.00..241138.20 rows=8722020 width=29)
-> Parallel Hash (cost=456815.59..456815.59 rows=5266 width=209)
-> Parallel Seq Scan on "Person" p (cost=0.00..456815.59 rows=5266 width=209)
Filter: (properties @> agtype_build_map('primaryName'::text, '"Christian Bale"'::agtype))
-> Index Scan using imdb_dev_title_hash_id on "Title_hash" (cost=0.00..1.32 rows=1 width=8)
Index Cond: (id = e.end_id)
(11 rows)
```
### Rationale
Because Title_hash has less data per row and it is indexed, joining it would be faster than joining with the original Title table. The cost of these query is almost similar.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org