CODEWITHSUNDEEP
rubypostgresqlelasticsearchfull-text-searchsearchkick
Matt Welke
Matt Welke

Reputation: 1858

Why do Postgres full text search and Elasticsearch rank results differently?

I was wondering if someone with experience implementing full text search could shed some light on my strange results when comparing Postgres's full text search with Elasticsearch.

I use a pair of Rails apps to test them, each with the same model (but with different gems, 'textacular' for the pg test, 'searchkick' for the es test) and the same test data:

# seeds.rb

def make_post(body)
  {
    title: 'A Post About Fruits',
    body: body,
    num_likes: 0
  }
end

Post.destroy_all

Post.create([
  make_post('I like apples.'),
  make_post('I like bananas.'),
  make_post('I like apples and bananas.'),
  make_post('I like oranges.'),
  make_post('I like.')
])

But when I run a bunch of searches on them, the results seem to make more sense with Postgres sometimes, make more sense with Elasticsearch sometimes, and they often contradict each other in behavior. In the following results, I list the top two posts returned for each search term, or one post or zero if that's all that was returned:

Search for:

'apples':

pg: 1. 'I like apples.' 2. 'I like apples and bananas.'

es:

  1. 'I like apples and bananas.'
  2. 'I like apples.'

'bananas':

pg: 1. 'I like bananas.' 2. 'I like apples and bananas.'

es: 1. 'I like bananas.' 2. 'I like apples and bananas.'

'apples and':

pg: 1. 'I like apples.' 2. 'I like apples and bananas.'

es: 1. 'I like apples and bananas.'

'apples and bananas':

pg: 1. 'I like apples and bananas.'

es: 1. 'I like apples and bananas.'

'I like apples.':

pg: 1. 'I like apples.' 2. 'I like apples and bananas.'

es: 1. 'I like apples and bananas.' 2. 'I like apples.'

'app':

pg: no results

es: 1. 'I like apples and bananas.' 2. 'I like apples.'

'appl':

pg: 1. 'I like apples.' 2. 'I like apples and bananas.'

es: 1. 'I like apples and bananas.' 2. 'I like apples.'

I have to admit, this is with default settings, I did no tuning or using custom query syntax (to do AND vs OR etc).

Upvotes: 3

Views: 1673

Answers (1)

dshockley
dshockley

Reputation: 1494

You are getting weird results from Elasticsearch, because some statistics are computed across a single shard, not across the entire index. Usually this is fine, because most document collections are large, but when you only have a few documents in a shard, those statistics don't make a lot of sense. In your case I think the statistic in question that's a problem is avgFieldLength, which contributes to tfNorm score. Try creating a new index with just one shard:

PUT /testindex
{
  "settings": {
    "index": {
      "number_of_shards": 1
    }
  }
}


POST /testindex/doc/1
{
  "body": "I like apples."
}


POST /testindex/doc/2
{
  "body": "I like apples and bananas."
}

Then the query:

POST /testindex
{
  "query": {
    "query_string": {
      "query": "apples"
    }
 }

Then you should see the rank:

  1. I like apples.
  2. I like apples and bananas.

In case you want to figure out what's going on with ranking, you can use explain:

POST /testindex
{
  "explain": true,
  "query": {
    "query_string": {
      "query": "apples"
    }
 }

All that being said, you should not expect postgres search ranking to match elasticsearch ranking. Elasticsearch uses normalized tf-idf score, and postgres does not consider document frequency or document length. See this question for more information: Does PostgreSQL use tf-idf?

Upvotes: 3

Related Questions