If I understood correctly, a user query is first applied to the `SearchItem` table (using the `search_vector` field?). Then the returned `filters` are used to generate a normal (not full-text search) SQL query that would retrieve items from the product table?
That's correct! The second step therefore doesn't have any "fuzziness" to it, it's a strict inclusion/exclusion filter across products based on their attributes.
Question: are they using "filters" as facets or what FAST called refiners? That's the impression I had on a read through. Endeca (now something inside Oracle) had some early success with faceted search for ecommerce.
Yes I believe some systems call these facets. We did some investigation into faceted search systems, but found that by combining the faceted search in the same process as the free text search, tuning the system would be more complex and it would be possible to result in poor search results often.
Essentially we forced all queries to be either only faceted search, or to fall back to full text across descriptions. For our use case, almost all queries could be satisfied by facets, so it didn't make sense to include any non-faceted search results in the results for those queries.
As someone also dealing with "your search sucks", I felt all of this pain.
I think sticking with Postgres was a mistake, though. Denormalizing into something like ElasticSearch would probably have gotten you further (and it's so fast).
Recently had a good experience with Typesense [0] for implementing ecommerce search. We solved the "blue shirts" problem by grouping a products color variants together using `group_by` and ranking by text match. The best "matching" shirts are then likely blue. This is a bit simplified, and not exactly the same case, but it worked surprisingly well.
One of the issues for us is that we wanted to rank by item suitability for the user, not by relevance. When using FTS, this didn't work because you'd get a non-blue shirt that would be better for the user and that showed up first. But when using the filter-based search, that couldn't happen because the recommendation algorithm was only getting blue shirts to rank, so all of them were relevant, and the same level of relevance.
I don't totally understand that middle step "So why not search filters instead of products". It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where?
When we get to the last step, of implementing bitmaps for filters, and then trying to optimize to deal with the RAM implications... why not just use Solr or ElasticSearch instead of reinventing the wheel? That's basically what lucene (which Solr and ES are built on) is doing, something like that implementation you came up with, but with lots of optimizations and tunings on top of the naive implementation.
> It just means searching over the human-edited metadata instead of free-for-all free text fields that were scraped from who knows where?
Not quite. Rather than the free text search being over products, it's over combinations of filters. These filters can then be applied to produce "exact" results, rather than "fuzzy" results based on textual matches.
> why not just use Solr or ElasticSearch instead of reinventing the wheel
A few reasons. Firstly, the faceted search in these took a fair bit of effort to expose to users in a way that wouldn't result in bad results (like "blue shirt" returning blue non-shirts, and non-blue shirts)
Secondly, these are new infrastructure components that take a lot of work to run. ElasticSearch is notorious for being a pain to operate. We were a small engineering team, only 3-5 backend engineers in the company at the time (depending on how you count), running the whole stack, infra, everything.
My solution to this problem had been to put the category name and all other product attributes in a full text search field and then index it. So that a search for "blue shirt" will select all shirt even if the title doesn't include it.
And then fine tune what to return by putting "and" or "or" in between keywords based on that search engine's behavior.
Yeah that would have been a pretty good intermediate option between our first and second passes at the issue. I think it would have still returned some incorrect results where we had overlap in category naming that was only apparent in context (e.g. jackets – coat jackets vs dinner jackets), but still probably a good improvement.
This is actually where something like Elasticsearch would work significantly better than Postgres Full Text Search. PFTS is pretty good when you have a small data set and all of the relevant keywords are in the product data, but as OP discovered, it falls pretty far short of the mark when you have anything even remotely complicated.
But putting that aside for a moment, most of the effort here actually has nothing to do with which engine you are using to power your search. For this kind of challenge, it doesn't matter if you use Elasticsearch, Algolia, or Postgres Full Text Search! Instead, the problem space has a lot more to do with your data and how we can teach a computer to interpret it the way a human would.
In the e-commerce/search space, one of the biggest challenges is what I call "query understanding," which is breaking down the user's query and trying to understand what they meant. This isn't trying to be "smart" and show you things you aren't looking for, but rather understand what the underlying intent is and filter on _that_.
In OP's scenario, you aren't searching for things with the word "blue" and "shirt" in it. You're actually searching for one thing: a shirt, with a (strong) preference for blue!
Most search engines, by default, will do something to the effect of "SELECT * FROM products WHERE anyfield CONTAINS 'blue' AND anyfield CONTAINS 'shirt'" and just return everything. But as OP discovered, that might return a product where "blue pants made by the people who brought you your favorite pink shirt," which definitely isn't what you want.
Instead, you look at the query: what are we looking for here? We're looking for a shirt. So the entity is "shirt" and the modifier is "blue." Typically "entities" represent "categories" or "product types", but often are more granular than what we would put in those filters. Thus, when we construct the information architecture for an e-commerce platform, we need to include specific types of data that represent higher-order abstractions of the product that we're looking at. Cheezits aren't just Cheezits -- they're "crackers", and more specifically, "cheese crackers." Milk isn't just milk, it's a beverage and it's dairy.
We now have this concept of an entity ("shirt"), and a modifier of "blue", which through various processes we have determined is a color, a theme, or perhaps a pattern. Then we can return a list of all of the shirts, prioritizing the blue ones.
Complicating things is this: is navy considered "blue"? What about teal? Turquoise? Cyan? It's important to be precise about the colors for categorization, thumbnail, filtering/faceting, and UX purposes, but when people type in search queries, they often are not as precise as their brains are.
When sorting the results, should we prioritize proper "blue" over navy or teal? If someone types in "shirt" and then clicks the "blue" filter, what are we showing and hiding based on that?
We obviously need to be a little bit smarter here....
...and now we're down this giant rabbit hole of how to optimize search. Clearly, throwing things into a Postgres Full Text Search isn't going to cut it. We're going to need to leverage more annotated data (garbage in, garbage out -- make sure your product data is properly annotated!), better query understanding, better sorting, and faceting/filtering!
And, honestly, most of this isn't even about whether we're using Elasticsearch or Postgres or Algolia -- a lot of this is taking your meatspace brains and thinking about what words really mean, how people type them in, and how to interpret those words into something that returns something meaningful.
This ain't Google, but it also ain't a phone book. There are tons and tons of weird...
This is a Hall-of-Fame Hacker News comment! A concise, insightful, entertaining explanation of something that has unexpected layers of nuance - and pound-for-pound one of the best descriptions of faceted search I've seen anywhere (and I'm a full-time eCommerce technology nerd!)
Thanks for taking the time to write this, comments like these are why I'm still a daily Hacker News reader after 10 years :-)
I'm interested how this compares to the second phase of our search implementation. I realise that searching filters was a somewhat limited implementation and not a general solution to search, but it seems like it was pretty much an implementation of what you've described. Certainly, my understanding of our problem was essentially what you've described.
Sort of! Your approach of "searching filters" is certainly a shortcut into larger query understanding, and for your use-case, depending on the size and variety of your catalog, it may be totally sufficient. You're actually light-years ahead of most search engines with this strategy! Awesome work.
Where I might go next with this would be to investigate the search results and evaluate the quality of those results, and then improve that over time by increasing your query understanding of what the users are asking for.
One question to ask yourself is "What is the purpose of search?" My answer is "to find what I'm looking for as fast as possible."
Remember, time is money, and if it takes customers longer to find what they're looking for on your site versus another e-commerce site (or worse yet, an actual physical store), then they'll leave and go to the other site. Speed and low effort is key!
(Note: If you are happy with conversion rates, then you can stop here -- don't go down rabbit holes you don't need to go down. There are other cool projects to work on, too!)
So, given this idea of making this as efficient for customers as possible, what's the least amount of effort (and therefore the fastest) way for a customer to purchase something?
The answer, of course, is to find it on the first try! (This reminds me of that scene in Happy Gilmore where Happy hits a hole in one, and he says to his coach, "I should just do that every time.")
Imagine I search for `blue shirt`, and the shirt I wind up buying is the 12th item on the list. From a quantitative standpoint, the conversion rate is 100%. I searched for something, and then I bought it! But qualitatively, the user experience is sub-par: you showed me eleven things I wasn't interested in before the 12th thing that I was interested in! This is cognitive load, and every time I have to look at the _next_ item in the list (or worse, go to the second -- or god forbid, the third! -- page), that's a chance that I'll give up and leave, or conduct another search and be equally frustrated.
The goal here, then, is to try and get the right item in the first slot 100% of the time. This is a bit of a holy grail, but it's a good target to aim for.
If your users' queries are simple and straightforward and your catalog is sufficiently small and focused, simply doing ANDs against filters might be good enough, but if you have a huge variety, then you may wind up having the "right" blue shirt be pretty far down the list.
How do you fix this? Well, first you need data. Track conversion rates, track which index position the conversion, and look at keywords that people are looking for. Also look at search queries that result in zero hits! Consider typo correction and synonyms to ensure maximum coverage of those queries.
If you start identifying keywords that folks are searching for but finding zero or low conversion, then you have some interesting information! Either start carrying products that match those terms, or start tagging relevant products with those terms to increase conversion rates and reduce zero-hit results.
Then you possibly need some sort of popularity metric (sales volume over a period of time is a good one) or some sort of personalization engine (looks like you have something, but I generally don't recommend it for folks just starting out). That way the most popular "blue shirt" will be first -- the odds are fantastic that your customer's preferences are in line with the majority -- and this should boost the conversion index to be closer to that first slot.
Also, as sort of a tangent, IMO it's also better to say "Hey, we don't have that," than to return bad stuff. Again, imagine that I search for `blue shirt` and you don't actually have any blue shirts. In that case, you may drop `blue` and return `shirts`, or maybe you drop `shirts` and return `blue`, and now I see red shirts and blue pa...
17 comments
[ 4.8 ms ] story [ 45.3 ms ] threadIf I understood correctly, a user query is first applied to the `SearchItem` table (using the `search_vector` field?). Then the returned `filters` are used to generate a normal (not full-text search) SQL query that would retrieve items from the product table?
Essentially we forced all queries to be either only faceted search, or to fall back to full text across descriptions. For our use case, almost all queries could be satisfied by facets, so it didn't make sense to include any non-faceted search results in the results for those queries.
I think sticking with Postgres was a mistake, though. Denormalizing into something like ElasticSearch would probably have gotten you further (and it's so fast).
Searching the filters is pretty clever, though.
[0] https://typesense.org/
But this means there are then non-blue shirts.
One of the issues for us is that we wanted to rank by item suitability for the user, not by relevance. When using FTS, this didn't work because you'd get a non-blue shirt that would be better for the user and that showed up first. But when using the filter-based search, that couldn't happen because the recommendation algorithm was only getting blue shirts to rank, so all of them were relevant, and the same level of relevance.
When we get to the last step, of implementing bitmaps for filters, and then trying to optimize to deal with the RAM implications... why not just use Solr or ElasticSearch instead of reinventing the wheel? That's basically what lucene (which Solr and ES are built on) is doing, something like that implementation you came up with, but with lots of optimizations and tunings on top of the naive implementation.
Not quite. Rather than the free text search being over products, it's over combinations of filters. These filters can then be applied to produce "exact" results, rather than "fuzzy" results based on textual matches.
> why not just use Solr or ElasticSearch instead of reinventing the wheel
A few reasons. Firstly, the faceted search in these took a fair bit of effort to expose to users in a way that wouldn't result in bad results (like "blue shirt" returning blue non-shirts, and non-blue shirts)
Secondly, these are new infrastructure components that take a lot of work to run. ElasticSearch is notorious for being a pain to operate. We were a small engineering team, only 3-5 backend engineers in the company at the time (depending on how you count), running the whole stack, infra, everything.
And then fine tune what to return by putting "and" or "or" in between keywords based on that search engine's behavior.
But putting that aside for a moment, most of the effort here actually has nothing to do with which engine you are using to power your search. For this kind of challenge, it doesn't matter if you use Elasticsearch, Algolia, or Postgres Full Text Search! Instead, the problem space has a lot more to do with your data and how we can teach a computer to interpret it the way a human would.
In the e-commerce/search space, one of the biggest challenges is what I call "query understanding," which is breaking down the user's query and trying to understand what they meant. This isn't trying to be "smart" and show you things you aren't looking for, but rather understand what the underlying intent is and filter on _that_.
In OP's scenario, you aren't searching for things with the word "blue" and "shirt" in it. You're actually searching for one thing: a shirt, with a (strong) preference for blue!
Most search engines, by default, will do something to the effect of "SELECT * FROM products WHERE anyfield CONTAINS 'blue' AND anyfield CONTAINS 'shirt'" and just return everything. But as OP discovered, that might return a product where "blue pants made by the people who brought you your favorite pink shirt," which definitely isn't what you want.
Instead, you look at the query: what are we looking for here? We're looking for a shirt. So the entity is "shirt" and the modifier is "blue." Typically "entities" represent "categories" or "product types", but often are more granular than what we would put in those filters. Thus, when we construct the information architecture for an e-commerce platform, we need to include specific types of data that represent higher-order abstractions of the product that we're looking at. Cheezits aren't just Cheezits -- they're "crackers", and more specifically, "cheese crackers." Milk isn't just milk, it's a beverage and it's dairy.
We now have this concept of an entity ("shirt"), and a modifier of "blue", which through various processes we have determined is a color, a theme, or perhaps a pattern. Then we can return a list of all of the shirts, prioritizing the blue ones.
Complicating things is this: is navy considered "blue"? What about teal? Turquoise? Cyan? It's important to be precise about the colors for categorization, thumbnail, filtering/faceting, and UX purposes, but when people type in search queries, they often are not as precise as their brains are.
When sorting the results, should we prioritize proper "blue" over navy or teal? If someone types in "shirt" and then clicks the "blue" filter, what are we showing and hiding based on that?
We obviously need to be a little bit smarter here....
...and now we're down this giant rabbit hole of how to optimize search. Clearly, throwing things into a Postgres Full Text Search isn't going to cut it. We're going to need to leverage more annotated data (garbage in, garbage out -- make sure your product data is properly annotated!), better query understanding, better sorting, and faceting/filtering!
And, honestly, most of this isn't even about whether we're using Elasticsearch or Postgres or Algolia -- a lot of this is taking your meatspace brains and thinking about what words really mean, how people type them in, and how to interpret those words into something that returns something meaningful.
This ain't Google, but it also ain't a phone book. There are tons and tons of weird...
Thanks for taking the time to write this, comments like these are why I'm still a daily Hacker News reader after 10 years :-)
I'm interested how this compares to the second phase of our search implementation. I realise that searching filters was a somewhat limited implementation and not a general solution to search, but it seems like it was pretty much an implementation of what you've described. Certainly, my understanding of our problem was essentially what you've described.
Where I might go next with this would be to investigate the search results and evaluate the quality of those results, and then improve that over time by increasing your query understanding of what the users are asking for.
One question to ask yourself is "What is the purpose of search?" My answer is "to find what I'm looking for as fast as possible."
Remember, time is money, and if it takes customers longer to find what they're looking for on your site versus another e-commerce site (or worse yet, an actual physical store), then they'll leave and go to the other site. Speed and low effort is key!
(Note: If you are happy with conversion rates, then you can stop here -- don't go down rabbit holes you don't need to go down. There are other cool projects to work on, too!)
So, given this idea of making this as efficient for customers as possible, what's the least amount of effort (and therefore the fastest) way for a customer to purchase something?
The answer, of course, is to find it on the first try! (This reminds me of that scene in Happy Gilmore where Happy hits a hole in one, and he says to his coach, "I should just do that every time.")
Imagine I search for `blue shirt`, and the shirt I wind up buying is the 12th item on the list. From a quantitative standpoint, the conversion rate is 100%. I searched for something, and then I bought it! But qualitatively, the user experience is sub-par: you showed me eleven things I wasn't interested in before the 12th thing that I was interested in! This is cognitive load, and every time I have to look at the _next_ item in the list (or worse, go to the second -- or god forbid, the third! -- page), that's a chance that I'll give up and leave, or conduct another search and be equally frustrated.
The goal here, then, is to try and get the right item in the first slot 100% of the time. This is a bit of a holy grail, but it's a good target to aim for.
If your users' queries are simple and straightforward and your catalog is sufficiently small and focused, simply doing ANDs against filters might be good enough, but if you have a huge variety, then you may wind up having the "right" blue shirt be pretty far down the list.
How do you fix this? Well, first you need data. Track conversion rates, track which index position the conversion, and look at keywords that people are looking for. Also look at search queries that result in zero hits! Consider typo correction and synonyms to ensure maximum coverage of those queries.
If you start identifying keywords that folks are searching for but finding zero or low conversion, then you have some interesting information! Either start carrying products that match those terms, or start tagging relevant products with those terms to increase conversion rates and reduce zero-hit results.
Then you possibly need some sort of popularity metric (sales volume over a period of time is a good one) or some sort of personalization engine (looks like you have something, but I generally don't recommend it for folks just starting out). That way the most popular "blue shirt" will be first -- the odds are fantastic that your customer's preferences are in line with the majority -- and this should boost the conversion index to be closer to that first slot.
Also, as sort of a tangent, IMO it's also better to say "Hey, we don't have that," than to return bad stuff. Again, imagine that I search for `blue shirt` and you don't actually have any blue shirts. In that case, you may drop `blue` and return `shirts`, or maybe you drop `shirts` and return `blue`, and now I see red shirts and blue pa...