8 ms·

This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else) We begrudgingly use Solr instead (we started before

This is the key one for us that makes Postgres a non-starter for FTS (We use postgres for everything else) We begrudgingly use Solr instead (we started before ES was really a thing and haven't found a need to switch yet) When you get more than about two different types of filters (e.g. types of filters could be 'tags', 'categories', 'geotags', 'media type', 'author' etc), the combinatorial explosion of Postgres queries required to provide facet counts gets unmanageable. For example, when I do a query filtered by `?tag=abc&category=category1`, I need to do these queries: - `... where tag = 'abc' and category_id = 1` (the current results) - `count(*) ... where tag = 'def' and category_id = 1` (for each other tag present in the results) - `count(*) ... where tag = 'abc' and category_id = 2` (for each other category present in the results) - `count(*) ... where category_id = 1` - `count(*) ... where tag = 'abc'` - `count(*)` There are certainly smarter ways to do this than lots of tiny queries, but all this complexity still ends up somewhere and isn't likely to be great for performance. Whereas solr/elasticsearch have faceting built in and handle this with ease.

kalev 5y ago
This is exactly the issue I’m currently facing. We do a bunch of count queries to calculate facets and am looking for something that can do this out of the box. I’m glad I came to the same conclusion myself, either solr or elasticsearch might be the way to go. Starting this from scratch, which of the two would you recommend and why?
- Ueland 5y ago
  I myself is currently setting up Solr for a project as my experience with ES as a DevOps is not a happy one. Always nodes/indexes having some kind of problem. In the same time i have also worked with Solr for years before and never met any major issues. It just works and does the job well.
- craigds 5y ago
  Unfortunately I don't know enough about elasticsearch to provide a useful comparison. I like the way ES queries are structured JSON instead of obscure compact querystring parameters, but that's not a good reason to choose one over the other :)
radiospiel 5y ago
You could run this in a single query `count(*) .. group by (tag, category_id)` and then post process the results. counting by groups is still relatively expensive though in postgres; I built an implementation which uses estimate counting (via query planner) for larger buckets, and exact counting for only smaller buckets, but my use case allowed for some degree of inconsistency.

kalev 5y ago

This is exactly the issue I’m currently facing. We do a bunch of count queries to calculate facets and am looking for something that can do this out of the box. I’m glad I came to the same conclusion myself, either solr or elasticsearch might be the way to go. Starting this from scratch, which of the two would you recommend and why?

Ueland 5y ago
I myself is currently setting up Solr for a project as my experience with ES as a DevOps is not a happy one. Always nodes/indexes having some kind of problem. In the same time i have also worked with Solr for years before and never met any major issues. It just works and does the job well.
craigds 5y ago
Unfortunately I don't know enough about elasticsearch to provide a useful comparison. I like the way ES queries are structured JSON instead of obscure compact querystring parameters, but that's not a good reason to choose one over the other :)

radiospiel 5y ago

You could run this in a single query `count(*) .. group by (tag, category_id)` and then post process the results. counting by groups is still relatively expensive though in postgres; I built an implementation which uses estimate counting (via query planner) for larger buckets, and exact counting for only smaller buckets, but my use case allowed for some degree of inconsistency.