Ggtcf

I have a large number of nodes representing accounts, which we could label as say (a :Account). Each (:Account) can have potentially tens of thousands of (t :Transaction) nodes connected to it, each representing the data for a transaction that occurred involving that account.

The (:Transaction) nodes have a date property. Given a date to query on what would be the most efficient way to get the latest (:Transaction) node for each (a :Account) that occurs before or on the query date? This could be one way to do it:

// run for all address nodes
match (a :Address)
with distinct a
optional match (a)-->(t :Transaction)
where t.timestamp <= date("2014-03-07")
with a, t
where t.date = max(t.date)
return a, t

However I'm not sure if this method is very efficient when the number of (t) connected to each (a) becomes very large. Is there a way to write the query or to index the database such that the query time scales linearly with the number of accounts, no matter the number of transactions connected to those account nodes?

For disclosure I posted a version of this question on the neo4j community forum, but I'm hoping the greater traffic on this site gives this question more exposure.

In neo4j 3.5, a new "index-backed order by" optimization was added. This means that if you create a "native" index (see here for the details), then the index will be stored in sorted order, and the ORDER BY clause on a property on which the index is used won't actually have to do any sorting.

So, assuming that you have created in index on :Transaction(timestamp), like so:

CREATE INDEX ON :Transaction(timestamp);

then, in neo4j 3.5+, this query (with an optional hint to use that index) should avoid any sorting when finding the Transaction with the maximum timestamp for each Address:

MATCH (a:Address)-->(t:Transaction)
USING INDEX t:Transaction(timestamp)
WHERE t.timestamp <= date("2014-03-07")
WITH a, t
ORDER BY t.timestamp DESC
RETURN a, COLLECT(t)[0] AS transaction

This query should do the following:

1. Use the index to get all Transaction nodes with an appropriate timestamp (in descending order, without sorting).


2. Get the Address nodes related to each Transaction.


3. For each distinct Address node, create a list of all the related Transaction nodes (in descending timestamp order, without sorting), and get the first one from the list.


4. Return each distinct Address node and its most recent appropriate Transaction node.

This query will scale linearly with the number of appropriate Transactions. If your use case permits it, you could get faster results by reducing the number of appropriate Transactions by also putting a lower bound in your WHERE clause.