I´m new to Elastic search but we have been using Lucene for many years and are on the way to move to Elastic instead.
We index the most important data in an ERP system (items, customers, orders etc) which ends up to almost 300 different indexes. Each can easliy contain 10 million records. With the current implementation we have a problem with certain searches that goes cross mulitple indexes like equal to value x in index A and value y+z in index B and value w in index C. The result is maybe 20 hits that equals all of these conditions. The issue is that each sub search can generate a few million hits. Our current implementation supports both this search as well as a role-up hirarchial search (starting in index a, takes hits and moves on to search in index b but only on record matching first search) . This works fine but not with these volumes at each search. To add to complexity the primary keys of the idexes are not identical but parts of the keys are in all indexes primary keys. The data is also for most highly changable and need to as close as possible on-line synced (delay of 30s to 2 minutes are ok). We have tried to explore the path of virtual indexes as well as creating a true index mergin the needed data elements into one and then use delta updates of the index with only changed columns. Unfortunately this far we have not managed to get a solution that also performs well enough.
Does anyone here have any idea of if and how we can solve this in Elastic search?