On Wed, Apr 19, 2017 at 8:09 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
Your best bet might be to ignore the per-field searching in the initial (indexed) pass of the query to get everything that has all the search terms, regardless of which field they occur in. And the re-check whether each of the found values was found in the appropriate field in a later pass.Something likeselect * from sample whereto_tsvector(json_thing->>:key1) @@ :value1 and to_tsvector(json_thing->>:key2) @@ :value2 and to_tsvector('english',json_thing) @@ (:value1 || :value2)
that worked pretty well when there was an AND condition with multiple k:v pairs as you have. However replacing it with an OR condition across k:v pairs it was pretty slow. I do like the simplicity though. Maybe indexing the 10ish most common columns would be a "good enough" solution.
From the initial email:> An idea that has come up is to use a materialized view or secondary table with triggers, where we would have 3 columns (id, key, value).How would this be different from the "triple store" you are abandoning?
it would be fairly similar. One advantage would be that we could simplify the backend to just a RDMS (which we use already), and not have to maintain a separate "triple store" instance
