Hi All,
I would like to have your opinion and maybe hints on how to improve the way we are indexing the data, from a query performance perspective.
As a foreword, we already tried to flatten as much as possible but we still need to consider "nested" data.
Let me explain (this is a simplified view):
-
We have a movement, which contains:
-
data directly related to the movement
-
traders (might be multiple). Each trader has multiple data.
-
gooditems (might be multiple). Each good item has:
- traders (might be multiple). Each trader has multiple data.
- documents (might be multiple). Each document has multiple data
-
We need to be able to query on anything and to combine anything, such as:
- Look for movements with conditions on movement data, which conditions on gooditems, under which we could also put conditions on gooditems instances. And so forth.
We mapped as follows:
movement
- data
- nested traders
- data
- nested gooditems
- data
- nested traders
- data
- nested documents
- data
...
- data
This results in generating query statements which involve sometimes lots of "nested".
We need to consider performance and our system counts billions of documents.
My fear is on the performance side...
You guys, specialists, could you please tell if this is the correct way to do it or, are there any better ways to do it?
In advance, many thanks