| After talking to Lynsay Wright from Estate Reporting, we found that this query is not hardcoded, it based on user input. So the user may do a query based on other fact from the few hundreds available.
So improving just on query out of potential hundreds might not be such a good idea, because the indexes might take a long time to create and require additional space.
Plus the user might use custom facts that we are not aware of. Anyway, we did some testing on the 100k nodes sandbox with 20 users, but on the n1 server and the query took on average 17707 ms.
Then we added an index on the operatingsystem fact and on average, the request took 7123 ms. That's an improvement of approximately 60%. The sql command we used to create the index was:
CREATE INDEX operating_system_values_idx ON factsets USING GIN (((stable||volatile)->>'operatingsystem') gin_trgm_ops);
|
|
