In myworld, there are many cities and each city can have zero-to-millions of residents.
Each city has a property called "GenderOfMayor", and residents have some properties like name,age.
All of my search cases are to find residents based on some of resident's property and GenderOfMayor(say get all residents with Name='XXX' where GenderOfMayor(GoM) is Female/male).
Some of the characteritics of the data I am building are
- There are a few thousands of cities, but there can be millions of residents in them
- The genderOfMayor changes but are infrequent.
- The values of GenderOfMayor is a enum (handful of distinct values)
I want to optimize for better query performance than indexing performance.
Option 1: stamp gender of Mayor in each resident's document.
Queries would be "where Name ="XXX" and GoM=F"
Pros:optimized for query
Cons:when GoM changes for a large city, we would have to update millions of documents (this can be tolerable)
Option 2: for each distinct values of GoM, have a document that contains all cities that share same GoM
Queries would be based on terms filter lookup.
Pros: quick to react to any changes in GoM
cons: a lookup on thousand values could be costly
Option 3: The Map of cities to GoM can be maintained outside Elastic search
We would have to pass the list of cities to queries each time (the list of cities can be in thousands).
My question is, with option2&3 for a give GoM there would be thousands of cities and having to put thousands of entries in scope filter, how bad would be query performance compared to Option #1, (I am worried as for each document ES has to do contains check on a thousand value list)
Option #1 has some cons, but they are tolerable in my case.
or would other options like parent/child help without affecting query performance.