I have a table of people, each person has a color and they seek various other colors and require various skills.
So I can query this I’ve created a table like so:
id, color_is_red, color_is_green, color_is_blue, color_is_yellow,
seeks_red, seeks_green, seeks_blue, seeks_yellow, seeks_technical, seeks_creative, seeks_leadership
I can then query the table like so:
-- Searching for green & yellow people that looking for red people with skills creative or technical select id from person WHERE color_is_red = false AND color_is_blue = false AND seeks_red = true AND ( seeks_creative = true OR seeks_technical = true ) limit 200
I’m unclear if this is the best way to structure this data. I also don’t know how I can index this. I had hoped to use a BLOOM index but bloom doesn’t support booleans. I think maybe I need a GIN index as I’ll be querying different combinations of the colors depending on whats required however I’ve tried creating a gin_btree index but the query planner isn’t using it. Another option would be a to use a bitset but I’m not sure how I would do this or index it.
Any help would be greatly appreciated!
Thanks very much