Performance issue with edges (very) large datasets

[smlankester]

As a proof of concept project we have tried to import the Panamapapers DB (as created for Neo4J).

I have created some Pytthon scripts for converting the nodes and edges csv files into SQL/Cypher staments,

For importing the data I have followed Eya Webinar demo on Youtube 32.15 / 51.57. (Used Posrtgres f attribute for processing the created SQL/Cypher statements files, (First the nodes then the edges).

In this we managed to import all nodes (53455 adresses, 213635 entities, 14110 intermediaries, and 235403 officers).

However, importing and querying of the relationships is problematic in terms of performance. (After 2 hours 2600 edges from the 674103).

I understand the difference in performance expectations between native graph databases and relationale database based solutions that should handle both.

In the current AGE extension solution i see and underrstand the reason of the ID (autoincrement) and Properties JSON fields.

I wonder if the performance will improve when it's possible add some indexes by adding for example two extra fields like Node id and Label. (Currently only available from the Properties JSON field so no database indexes can be created on for the relationship importent used ids stant and end id......)

[pdpotter]

See #45 for a previous discussion on the topic of unique properties / indexing.

There is a (1000 times faster) workaround for the creation of edges between vertices that were added earlier. The workaround consists of inserting the data directly in the underlying tables that are used by Apache AGE.

First, the underlying ids of the vertices are retrieved:

SELECT * FROM cyper('graph_name',$$
MATCH (n:LabelA)
return id(n), n.id
$$) as (id agtype, prop agtype);

A single edge is then created using the cypher function to make sure the underlying tables are created correctly (the id values are made up)

SELECT * FROM cypher('graph_name', $$
MATCH (d:LabelA), (r:LabelB)
WHERE id(d) = 11111 and id(r) = 11112
CREATE (d)-[:RelationA {prop: 'value'}]->(r)
$$) as (a agtype);

All other edges are created by direct insertion in the specific edge table (using executemany):

INSERT INTO graph_name.RelationA (start_id, end_id, properties)
VALUES ($1, $2, $3)

Where $1 are the domain_ids, $2 are the range_ids and $3 are the properties (as json dump).

Hopefully, property indexes will become available at some point in the future, so this workaround is no longer necessary.

[smlankester]

Thanks for your advice Pieterjan. I'm going to see if I can make some performance improvement with your advice and previous discussion #45. However, it probably only improves the inserts and not quering the nodes with relationships after importing all the data.. (?)

[pdpotter]

It indeed only improves the inserts and not the querying.

[pdpotter]

With 6279c10, GIN indices can now be used for querying as well (currently only in the {} in the MATCH clause, not yet in the WHERE clause (this should be available soon)).

[jrgemignani]

As our team inches closer to the full implementation of openCypher we have been directing more of our attention to performance, especially with queries. There have been a lot of performance patches in the last month and there will be some significant ones in the next month. These will be impacting queries, specifically. Some will be the addition of indexing, others will be optimizing query transforms.

john