MatheusFarias03 opened a new issue, #1555:
URL: https://github.com/apache/age/issues/1555
I have a graph with two vertex labels: `Wholesaler` and `Product`; and with
one edge label: `OFFERS`.
There are 3426 vertices with label `Product`, 4 vertices with label
`Wholesaler`, and 13326 edges with label `OFFERS`.
The below queries shows what are the properties of `Product` and `OFFERS`
(The names of the vertices are all in Portuguese).
```sql
SELECT * FROM cypher('TestGraph', $$
MATCH (P:Product)
RETURN P
$$) AS (product agtype);
product
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id": 2814749767109987, "label": "Product", "properties": {"name": "La
Vita Salada Verão", "type": "Salad", "description": "1 X 200 g"}}::vertex
{"id": 2814749767109988, "label": "Product", "properties": {"name": "La
Vita Salada Baby Summer Gourmet", "type": "Salad", "description": "1 X 100
g"}}::vertex
{"id": 2814749767109989, "label": "Product", "properties": {"name": "Daucy
Salada Verão Congelada", "type": "Salad", "description": "1 X 300 g"}}::vertex
{"id": 2814749767109990, "label": "Product", "properties": {"name": "Hiromi
Salada Americana", "type": "Salad", "description": "1 X 170 g"}}::vertex
{"id": 2814749767109991, "label": "Product", "properties": {"name": "Mix
Tropical Higienizado Oba Bem Querer", "type": "Salad", "description": "1 X 160
g"}}::vertex
(...)
```
```sql
SELECT * FROM cypher('TestGraph', $$
MATCH ()-[E:OFFERS]->()
RETURN E
$$) AS (offer agtype);
offer
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id": 2251799813698730, "label": "OFFERS", "end_id": 1970324836975960,
"start_id": 844424930132000, "properties": {"price": "R$ 44,00"}}::edge
{"id": 2251799813698731, "label": "OFFERS", "end_id": 1970324836975961,
"start_id": 844424930132000, "properties": {"price": "R$ 42,00"}}::edge
{"id": 2251799813698732, "label": "OFFERS", "end_id": 1970324836975962,
"start_id": 844424930132000, "properties": {"price": "R$ 38,00"}}::edge
{"id": 2251799813698733, "label": "OFFERS", "end_id": 1970324836975963,
"start_id": 844424930132000, "properties": {"price": "R$ 38,00"}}::edge
(...)
```
The following queries are focused on retrieving information about product
offerings from wholesalers, filtering for products that contain the word
'Vegano' ( 'Vegan' in Portuguese ) in the name.
```sql
-- With Cypher syntax.
WITH graph_query as (
SELECT * FROM cypher('TestGraph', $$
MATCH ()-[E:OFFERS]->(P:Product)
RETURN P.name, E.price ORDER BY P.name, E.price
$$) AS (product agtype, price agtype)
)
SELECT * FROM graph_query
WHERE graph_query.product::text LIKE '%Vegano%';
-- Time: 173.787 ms
-- With SQL syntax only.
SELECT o.id as offer_id,
w.properties->>'name' as wholesaler_name,
p.properties->>'name' as product_name,
o.properties->>'price' as product_price
FROM "TestGraph"."OFFERS" o
JOIN "TestGraph"."Wholesaler" w ON o.start_id = w.id
JOIN "TestGraph"."Product" p ON o.end_id = p.id
WHERE p.properties->>'name' LIKE '%Vegano%';
-- Time: 24.168 ms
```
Although I find a bit more understandable with the cypher syntax, the SQL
syntax returns the same result 7.19 times faster.
The query plan for the SQL syntax only is:
```
QUERY PLAN
---------------------------------------------------------------------------------
Hash Join (cost=110.49..478.78 rows=1 width=104)
Hash Cond: (o.start_id = w.id)
-> Hash Join (cost=109.40..477.66 rows=3 width=168)
Hash Cond: (o.end_id = p.id)
-> Seq Scan on "OFFERS" o (cost=0.00..318.26 rows=13326 width=80)
-> Hash (cost=109.39..109.39 rows=1 width=104)
-> Seq Scan on "Product" p (cost=0.00..109.39 rows=1
width=104)
Filter: ((properties ->> 'name'::text) ~~
'%Vegano%'::text)
-> Hash (cost=1.04..1.04 rows=4 width=104)
-> Seq Scan on "Wholesaler" w (cost=0.00..1.04 rows=4 width=104)
```
And the query plan for the Cypher syntax is:
```
QUERY PLAN
------------------------------------------------------------------------
CTE Scan on graph_query (cost=10.00..35.00 rows=1 width=64)
Filter: ((product)::text ~~ '%Vegano%'::text)
CTE graph_query
-> Function Scan on cypher (cost=0.00..10.00 rows=1000 width=64)
```
I'm building a website with AGE for my final project in college and I wanted
to better understand why the SQL query is faster than the one using Cypher.
--
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
