explain execution plan display

[kysmou]

explain
SELECT *
FROM cypher('graph_name', $$
MATCH (a:Person), (b:Person)
WHERE a.name = 'Node A' AND b.name = 'Node B'
with a,b
match(a)-[e:RELTYPE]->(b)
where b.born > 1999
RETURN a
$$) as (a agtype);
QUERY PLAN

Function Scan on cypher (cost=0.00..10.00 rows=1000 width=32)
(1 row)

Explain about Cypher is simply displayed as Function Scan on Cypher.
How can I see the plan information for the Cypher statement?

[pdpotter]

The auto_explain module can be used to get details about nested statements.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
SET auto_explain.log_nested_statements = true;

In my case, the info was written to the log file /var/log/postgresql/postgresql-11-main.log.

[kysmou]

Thanks for your guide.
I think the guide you gave is a guide using the parameters of the extension.
I was wondering if the existing explain and explain analyze functions of postgresql, which can only check the queries required by the user, do not apply to the current age cypher.

[jrgemignani]

I am currently attempting to add EXPLAIN, in its simple forms, to the openCypher grammar in AGE. But, at this point, I can't guarantee that it will be successful or useful.

John

[jrgemignani]

EXPLAIN has been added to the master repository. It is available by pulling the latest source down - not the latest release.

In order to use it, you will need to put the EXPLAIN inside of the cypher function. For example -

psql-11.5-5432-pgsql=# SELECT * FROM cypher('G', $$EXPLAIN MATCH (u)-[]->() RETURN u $$) AS (u agtype);
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..245939.57 rows=69877 width=32)
   Workers Planned: 2
   ->  Nested Loop  (cost=0.00..237951.87 rows=29115 width=32)
         Join Filter: (graphid_to_agtype(_age_default_alias_0_1.start_id) = graphid_to_agtype(u.id))
         ->  Parallel Append  (cost=0.00..70.91 rows=1617 width=8)
               ->  Parallel Seq Scan on edge1 _age_default_alias_0_1  (cost=0.00..15.71 rows=571 width=8)
               ->  Parallel Seq Scan on edge2 _age_default_alias_0_2  (cost=0.00..15.71 rows=571 width=8)
               ->  Parallel Seq Scan on edge3 _age_default_alias_0_3  (cost=0.00..15.71 rows=571 width=8)
               ->  Parallel Seq Scan on edge4 _age_default_alias_0_4  (cost=0.00..15.71 rows=571 width=8)
               ->  Parallel Seq Scan on _ag_label_edge _age_default_alias_0  (cost=0.00..0.00 rows=1 width=8)
         ->  Append  (cost=0.00..84.00 rows=3601 width=40)
               ->  Seq Scan on _ag_label_vertex u  (cost=0.00..0.00 rows=1 width=40)
               ->  Seq Scan on begin u_1  (cost=0.00..22.00 rows=1200 width=40)
               ->  Seq Scan on middle u_2  (cost=0.00..22.00 rows=1200 width=40)
               ->  Seq Scan on "end" u_3  (cost=0.00..22.00 rows=1200 width=40)
(15 rows)

psql-11.5-5432-pgsql=#

[kysmou]

I pulled the latest source down from age-github.
Tested as instructed by you and confirmed that the explain execution plan was shown.

SELECT * FROM cypher('test_graph', $$EXPLAIN MATCH p=(a)-[v]->(b)
RETURN p
$$) as (p agtype);
QUERY PLAN

Gather (cost=1000.00..143753.34 rows=35015 width=32)
Workers Planned: 2
-> Nested Loop (cost=0.00..139251.84 rows=14590 width=32)
Join Filter: (graphid_to_agtype(v_1.end_id) = graphid_to_agtype(b.id))
-> Nested Loop (cost=0.00..19871.84 rows=2430 width=96)
Join Filter: (graphid_to_agtype(v_1.start_id) = graphid_to_agtype(a.id))
-> Parallel Append (cost=0.00..17.73 rows=405 width=56)
-> Parallel Seq Scan on "RELTYPE" v_1 (cost=0.00..15.71 rows=571 width=56)
-> Parallel Seq Scan on _ag_label_edge v (cost=0.00..0.00 rows=1 width=56)
-> Append (cost=0.00..28.00 rows=1201 width=40)
-> Seq Scan on _ag_label_vertex a (cost=0.00..0.00 rows=1 width=40)
-> Seq Scan on "Person" a_1 (cost=0.00..22.00 rows=1200 width=40)
-> Append (cost=0.00..28.00 rows=1201 width=40)
-> Seq Scan on _ag_label_vertex b (cost=0.00..0.00 rows=1 width=40)
-> Seq Scan on "Person" b_1 (cost=0.00..22.00 rows=1200 width=40)
(15 rows)

Thank you.