Ideas for making a search performant?

Okay so I’ve got a fairly simple two sided marketplace going, people fill out a survey with a bunch of details about themselves, and companies provide product offerings with limitations that make them only apply to certain people. My goal is to identify which ones match and connect them.

Originally I had about 20 different potential product attributes that a company could limit on, and I simply forced a value to be entered for each one (even ones the company didn’t care about) so none of them could be blank. All of the attributes were numerical, so when I did the search I just looked for all the products that matched the search attributes.

However ideally I’d love to not have to hardcode sort of ugly looking limitations on each product aspect… leaving all of them blank except the ones that are relevant. Problem is… search seems to exclude customers if my search constraints compared a given user value against a null product value, eliminating them from the match.

I’ve been down a bunch of paths… biggest issue is performance. I can achieve what I need to with a series of really sluggish advanced filter queries but it takes forever, even on a very small dataset. Here’s what that looks like specifically (I’m using an API workflow for this with several other steps but I’ll focus on the actual matching query that’s giving me trouble):

There aren’t any constraints on the search, but then I do all the heavy lifting with a boatload of Advanced filters :joy:

Each advanced query is basically “This product’s [attribute] is empty or this product’s [attribute] is [greater/less than] this user’s [attribute value]” so I get to include products without a constraint.

As you can imagine, abysmally slow… the closest related approach I’ve come up with is to grab all products like above, but then minus list a search with a single advanced filter that identifies all products that get eliminated by an attribute (null values don’t cause me problems when I come at it from that side), but I think the order of operations here is getting in my way. Pretty sure the minus list is only listening to the next argument (a search for all products with no constraints) and then clearing out the list, rather than letting that search be limited by the advanced filter.


Hopefully that’s enough context, been banging my head against a wall for a while and I’d love to get another perspective. Thanks so much in advance!