[sandGorgon]

Could you talk about the usecase here ? This is very interesting from a db query tuning perspective. What kind of queries work well in scenarios like this ? I thought search engines are only useful in ranking based searches ...so you accept a degree of error margin wrt databases.

[atombender]

Any non-joining OLTP query will perform very well with ES. It is particularly effective with low-cardinality fields where in a traditional relational database you would not benefit from a B-tree index and a database like Postgres typically would revert to a sequential scan over the entire table. Column intersections in Lucene are extremely efficient, basically streaming sorted vectors of document IDs from RAM.

Where ES is not optimal is when you need joins. That said, doing left outer joins -- which is typical in web workloads where you may have something like an "articles" table that you want to query with filters and then join against "authors" and "categories" without filters to fetch connected data -- on the client side with some basic parallelization is surprisingly effective. Currently doing that in some apps where we get <100 millisecond performance even when fetching maybe 5-6 related objects per result.

[sandGorgon]

Do you do left outer join on elasticsearch...or do you do it in the client code ? I'm trying to figure out if elasticsearch supports these query types. It's something I never thought about.

[atombender]

In client code. ES doesn't do joins (ignoring a rather weak feature called "nested documents").