Search Postgresql Archives

Re: full text search on hstore or json with materialized view?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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 like

select * from sample where 
             to_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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux