# Moving the search from PostgreSQL to elasticsearch
**URL:** https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306
**Category:** Elasticsearch
**Created:** [January 11, 2013, 12:57am UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306 "2013-01-11T00:57:00Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@randel\_2](https://discuss.elastic.co/u/randel_2)
#### Post date: [January 11, 2013, 12:57am UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/1 "2013-01-11T00:57:00Z")
Hey, I was using PostgreSQL for searching so far since my applications
relies on it anyway. I found a way for scoring which I am pretty happy with
but search times are just inacceptable since it is searching in a table
with about 9 mio rows.
I started with elasticsearch just a few days ago in a PHP Project using the
elastica client. I indexed about a million documents with book titles and
some related information using the jdbc plugin but don't know if the
scoring I want is possible at all with elasticsearch. One maybe rather
unusual requirement is to different scoring for different users depending
on their language skills.
I have the following index of books which different editions each:
title | author | ratings | bookid | editionid
| languageid |
| --- |
| Hamlet |
| eng |
| Hamlet by Shakespeare |
| eng |
| Hamlet bilingual |
| eng |
| Romeo and Juliet |
| eng |
| Romeo und Julia |
| ger |
| Roméo et Juliette |
| fre |
| Othello |
| eng |
I want only one title of each bookid in the results - the one with the
highest score which is calculated as follows:
Take the highest ratings count of all results and call it max\_r.
Put a weight on the language - 100 for the uses first language, 5 for his
second spoken language, 3 for the third and 2 for the forth (in case
someone is so blessed with language skills) and call it lang\_weight. I have
the information of languages for logged in users.
score = weight \* 100/max\_r \* ratings
Is there a way to get the max\_r during the query and use it like in sql?
How can I tell elasticsearch to use the weight based on the languageid?
And I still would like to inegrate fuzzy search.
Here some examples of what I would like to get. Depending on the user I
would get following results when searching:
english user searching for 'hamlet':
1. editionid 1
only one result since the bookid shall be unique in a result set and
edition 1 has the highest order, score = 100 \* 0.036 \* 2784 = 10,000
editionid 2 has the score = 100 \* 0.036 \* 71 = 255
editionid 3 has the score = 100 \* 0.036 \* 144 = 517
german user (with second language eng and third fre) searching for 'romeo &
shakespeare':
1. editionid 5
because in his first language is ger, score = 100 \* 0.068 \* 325 = 2211
editionid 4 has the score = 5 \* 0.068 \* 1470 = 500
editionid 6 has the score = 3 \* 0.068 \* 487 = 99
spanish user (with second language german, third french, forth english)
searching for 'Shakespeare':
1. edition 1
score = 2 \* 0.036 \* 2784 = 200
2. edition 7
score = 2 \* 0.036 \* 1548 = 111
3. edition 5
score = 5 \* 0.036 \* 325 = 58
I would be very happy if you could point me to the right direction since
this is all quite confusing for me so far.
Thanks a lot.
--
---
### Author:  [@randel\_2](https://discuss.elastic.co/u/randel_2)
#### Post date: [January 11, 2013, 1:02am UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/2 "2013-01-11T01:02:11Z")
sorry for the table layout - where is the preview function?
## title | author | ratings| bookid | editionid | languageid
Hamlet |William Shakespeare | 2784 | 1 |
1 | eng
Hamlet by Shakespeare |William Shakespeare | 71 | 1 | 2
| eng
Hamlet bilingual |William Shakespeare | 144 | 1 | 3
| eng
Romeo and Juliet |William Shakespeare | 1470 | 2 | 4
| eng
Romeo und Julia |William Shakespeare | 325 | 2 | 5
| ger
Roméo et Juliette |William Shakespeare | 487 | 2 | 6
| fre
Othello |William Shakespeare | 1548 | 3
| 7 | eng
Am Freitag, 11. Januar 2013 01:57:00 UTC+1 schrieb randel:
> Hey, I was using PostgreSQL for searching so far since my applications
> relies on it anyway. I found a way for scoring which I am pretty happy with
> but search times are just inacceptable since it is searching in a table
> with about 9 mio rows.
>
> I started with elasticsearch just a few days ago in a PHP Project using
> the elastica client. I indexed about a million documents with book titles
> and some related information using the jdbc plugin but don't know if the
> scoring I want is possible at all with elasticsearch. One maybe rather
> unusual requirement is to different scoring for different users depending
> on their language skills.
>
> I have the following index of books which different editions each:
>
> title | author | ratings | bookid |
> editionid | languageid
>
> * * *
>
> Hamlet |William Shakespeare | 2784 | 1 | 1
> | eng
> Hamlet by Shakespeare |William Shakespeare | 71 | 1 | 2
> | eng
> Hamlet bilingual |William Shakespeare | 144 | 1 | 3
> | eng
> Romeo and Juliet |William Shakespeare | 1470 | 2 | 4
> | eng
> Romeo und Julia |William Shakespeare | 325 | 2 | 5
> | ger
> Roméo et Juliette |William Shakespeare | 487 | 2 | 6
> | fre
> Othello |William Shakespeare | 1548 | 3 | 7
> | eng
>
> I want only one title of each bookid in the results - the one with the
> highest score which is calculated as follows:
>
> Take the highest ratings count of all results and call it max\_r.
> Put a weight on the language - 100 for the uses first language, 5 for his
> second spoken language, 3 for the third and 2 for the forth (in case
> someone is so blessed with language skills) and call it lang\_weight. I have
> the information of languages for logged in users.
>
> score = weight \* 100/max\_r \* ratings
>
> Is there a way to get the max\_r during the query and use it like in sql?
> How can I tell elasticsearch to use the weight based on the languageid?
> And I still would like to inegrate fuzzy search.
>
> Here some examples of what I would like to get. Depending on the user I
> would get following results when searching:
>
> english user searching for 'hamlet':
>
> 1. editionid 1
> only one result since the bookid shall be unique in a result set and
> edition 1 has the highest order, score = 100 \* 0.036 \* 2784 = 10,000
> editionid 2 has the score = 100 \* 0.036 \* 71 = 255
> editionid 3 has the score = 100 \* 0.036 \* 144 = 517
>
> german user (with second language eng and third fre) searching for 'romeo
> & shakespeare':
>
> 1. editionid 5
> because in his first language is ger, score = 100 \* 0.068 \* 325 = 2211
> editionid 4 has the score = 5 \* 0.068 \* 1470 = 500
> editionid 6 has the score = 3 \* 0.068 \* 487 = 99
>
> spanish user (with second language german, third french, forth english)
> searching for 'Shakespeare':
>
> 1. edition 1
> score = 2 \* 0.036 \* 2784 = 200
> 2. edition 7
> score = 2 \* 0.036 \* 1548 = 111
> 3. edition 5
> score = 5 \* 0.036 \* 325 = 58
>
> I would be very happy if you could point me to the right direction since
> this is all quite confusing for me so far.
> Thanks a lot.
--
---
### Author:  [@radu\_gheorghe](https://discuss.elastic.co/u/radu_gheorghe)
#### Post date: [January 14, 2013, 5:33pm UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/3 "2013-01-14T17:33:14Z")
Hello,
I think you can use the Custom Score query for that:
> **[Elastic — The Search AI Company](https://www.elastic.co)**
>
> Power insights and outcomes with The Elastic Search AI Platform. See into your data and find answers that matter with enterprise solutions designed to help you accelerate time to insight. Try Elastic ...
The way I'll do it is to compute the score as weight/ratings (where you'd
have to compute weight on its own within the custom score script). This
should give you the same scoring order as your original formula (because
max\_r and 100 are constants within the same query).
If you need to show the score as your original formula, I think it's
cheaper to do that in your application. To get the highest rating I'd do a
second query, just to get the highest rating. I'd just use filters[0] and
sorting[1] to get that - in order to maximize the use of caches, and set
size=1 to get only the first result.
[0] [Elastic — The Search AI Company | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/query-filter.html)
--\> please note (see the bottom of the page) that this filter isn't cached
by default
[1] [Elastic — The Search AI Company | Elastic](http://www.elasticsearch.org/guide/reference/api/search/sort.html)
## Best regards, Radu
[http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene
On Fri, Jan 11, 2013 at 2:57 AM, randel [mandavister@gmail.com](mailto:mandavister@gmail.com) wrote:
> Hey, I was using PostgreSQL for searching so far since my applications
> relies on it anyway. I found a way for scoring which I am pretty happy with
> but search times are just inacceptable since it is searching in a table
> with about 9 mio rows.
>
> I started with elasticsearch just a few days ago in a PHP Project using
> the elastica client. I indexed about a million documents with book titles
> and some related information using the jdbc plugin but don't know if the
> scoring I want is possible at all with elasticsearch. One maybe rather
> unusual requirement is to different scoring for different users depending
> on their language skills.
>
> I have the following index of books which different editions each:
>
> title | author | ratings | bookid |
> editionid | languageid
>
> * * *
>
> Hamlet |William Shakespeare | 2784 | 1 | 1
> | eng
> Hamlet by Shakespeare |William Shakespeare | 71 | 1 | 2
> | eng
> Hamlet bilingual |William Shakespeare | 144 | 1 | 3
> | eng
> Romeo and Juliet |William Shakespeare | 1470 | 2 | 4
> | eng
> Romeo und Julia |William Shakespeare | 325 | 2 | 5
> | ger
> Roméo et Juliette |William Shakespeare | 487 | 2 | 6
> | fre
> Othello |William Shakespeare | 1548 | 3 | 7
> | eng
>
> I want only one title of each bookid in the results - the one with the
> highest score which is calculated as follows:
>
> Take the highest ratings count of all results and call it max\_r.
> Put a weight on the language - 100 for the uses first language, 5 for his
> second spoken language, 3 for the third and 2 for the forth (in case
> someone is so blessed with language skills) and call it lang\_weight. I have
> the information of languages for logged in users.
>
> score = weight \* 100/max\_r \* ratings
>
> Is there a way to get the max\_r during the query and use it like in sql?
> How can I tell elasticsearch to use the weight based on the languageid?
> And I still would like to inegrate fuzzy search.
>
> Here some examples of what I would like to get. Depending on the user I
> would get following results when searching:
>
> english user searching for 'hamlet':
>
> 1. editionid 1
> only one result since the bookid shall be unique in a result set and
> edition 1 has the highest order, score = 100 \* 0.036 \* 2784 = 10,000
> editionid 2 has the score = 100 \* 0.036 \* 71 = 255
> editionid 3 has the score = 100 \* 0.036 \* 144 = 517
>
> german user (with second language eng and third fre) searching for 'romeo
> & shakespeare':
>
> 1. editionid 5
> because in his first language is ger, score = 100 \* 0.068 \* 325 = 2211
> editionid 4 has the score = 5 \* 0.068 \* 1470 = 500
> editionid 6 has the score = 3 \* 0.068 \* 487 = 99
>
> spanish user (with second language german, third french, forth english)
> searching for 'Shakespeare':
>
> 1. edition 1
> score = 2 \* 0.036 \* 2784 = 200
> 2. edition 7
> score = 2 \* 0.036 \* 1548 = 111
> 3. edition 5
> score = 5 \* 0.036 \* 325 = 58
>
> I would be very happy if you could point me to the right direction since
> this is all quite confusing for me so far.
> Thanks a lot.
>
> --
--
---
### Author:  [@randel](https://discuss.elastic.co/u/randel)
#### Post date: [January 15, 2013, 4:25pm UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/4 "2013-01-15T16:25:18Z")
Hey Radu,
thanks for your reply. I used an extra query to find the max\_value just before realising that it doesn't change the order of scores. Now I use a custom script in order to get the language-dependend scoring:
$sort = array(
"\_script" =\> array(
'script' =\> " if(doc['languageid'].value==".$userlang[0]."){(doc['bookratings'].value \* 100) + (0.9/doc['case'].value);}
else if(doc['languageid'].value==".$userlang[1]."){(doc['bookratings'].value \* 5) + (0.9/doc['case'].value);}
else if(doc['languageid'].value==".$userlang[2]."){(doc['bookratings'].value \* 3) + (0.9/doc['case'].value);}
else if(doc['languageid'].value==".$userlang[3]."){(doc['bookratings'].value \* 2) + (0.9/doc['case'].value);}
else {doc['languageid'].value + (0.9/doc['case'].value);}",
'type' =\> 'number',
'order' =\> 'desc'),
);
That seems to work ok, even though it feels a little slower. And to have only the best fitting title shown of all editions of one book I wrote something in the application. It makes pagination more complicated but the grouping functionality isn't ready yet for elasticsearch. Even though it's implemented in Lucene already.
Regards,
Randel
---
### Author:  [@randel\_2](https://discuss.elastic.co/u/randel_2)
#### Post date: [January 15, 2013, 4:26pm UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/5 "2013-01-15T16:26:29Z")
Hey Radu,
thanks for your reply. I used an extra query to find the max\_value
just before realising that it doesn't change the order of scores. Now
I use a custom script in order to get the language-dependend scoring:
$sort = array(
"\_script" =\> array(
'script' =\> " if(doc['languageid'].value==".
$userlang[0]."){(doc['bookratings'].value \* 100) + (0.9/
doc['case'].value);}
else if(doc['languageid'].value==".$userlang[1].")
{(doc['bookratings'].value \* 5) + (0.9/doc['case'].value);}
else if(doc['languageid'].value==".$userlang[2].")
{(doc['bookratings'].value \* 3) + (0.9/doc['case'].value);}
else if(doc['languageid'].value==".$userlang[3].")
{(doc['bookratings'].value \* 2) + (0.9/doc['case'].value);}
else {doc['languageid'].value + (0.9/
doc['case'].value);}",
'type' =\> 'number',
'order' =\> 'desc'),
);
That seems to work ok, even though it feels a little slower. And to
have only the best fitting title shown of all editions of one book I
wrote something in the application. It makes pagination more
complicated but the grouping functionality isn't ready yet for
elasticsearch. Even though it's implemented in Lucene already.
Regards,
Randel
On 14 Jan., 18:33, Radu Gheorghe [radu.gheor...@sematext.com](mailto:radu.gheor...@sematext.com) wrote:
> Hello,
>
> I think you can use the Custom Score query for that:[Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/custom-score-q)...
>
> The way I'll do it is to compute the score as weight/ratings (where you'd
> have to compute weight on its own within the custom score script). This
> should give you the same scoring order as your original formula (because
> max\_r and 100 are constants within the same query).
>
> If you need to show the score as your original formula, I think it's
> cheaper to do that in your application. To get the highest rating I'd do a
> second query, just to get the highest rating. I'd just use filters[0] and
> sorting[1] to get that - in order to maximize the use of caches, and set
> size=1 to get only the first result.
>
> [0][Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/query-filter.html)
> --\> please note (see the bottom of the page) that this filter isn't cached
> by default
> [1][Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/sort.html)
>
> Best regards,
> Radu
> --[http://sematext.com/--](http://sematext.com/--) Elasticsearch -- Solr -- Lucene
>
> On Fri, Jan 11, 2013 at 2:57 AM, randel [mandavis...@gmail.com](mailto:mandavis...@gmail.com) wrote:
>
> > Hey, I was using PostgreSQL for searching so far since my applications
> > relies on it anyway. I found a way for scoring which I am pretty happy with
> > but search times are just inacceptable since it is searching in a table
> > with about 9 mio rows.
>
> > I started with elasticsearch just a few days ago in a PHP Project using
> > the elastica client. I indexed about a million documents with book titles
> > and some related information using the jdbc plugin but don't know if the
> > scoring I want is possible at all with elasticsearch. One maybe rather
> > unusual requirement is to different scoring for different users depending
> > on their language skills.
>
> > I have the following index of books which different editions each:
>
> > title | author | ratings | bookid |
> > editionid | languageid
>
> > * * *
> >
> > Hamlet |William Shakespeare | 2784 | 1 | 1
> > | eng
> > Hamlet by Shakespeare |William Shakespeare | 71 | 1 | 2
> > | eng
> > Hamlet bilingual |William Shakespeare | 144 | 1 | 3
> > | eng
> > Romeo and Juliet |William Shakespeare | 1470 | 2 | 4
> > | eng
> > Romeo und Julia |William Shakespeare | 325 | 2 | 5
> > | ger
> > Roméo et Juliette |William Shakespeare | 487 | 2 | 6
> > | fre
> > Othello |William Shakespeare | 1548 | 3 | 7
> > | eng
>
> > I want only one title of each bookid in the results - the one with the
> > highest score which is calculated as follows:
>
> > Take the highest ratings count of all results and call it max\_r.
> > Put a weight on the language - 100 for the uses first language, 5 for his
> > second spoken language, 3 for the third and 2 for the forth (in case
> > someone is so blessed with language skills) and call it lang\_weight. I have
> > the information of languages for logged in users.
>
> > score = weight \* 100/max\_r \* ratings
>
> > Is there a way to get the max\_r during the query and use it like in sql?
> > How can I tell elasticsearch to use the weight based on the languageid?
> > And I still would like to inegrate fuzzy search.
>
> > Here some examples of what I would like to get. Depending on the user I
> > would get following results when searching:
>
> > english user searching for 'hamlet':
>
> > 1. editionid 1
> > only one result since the bookid shall be unique in a result set and
> > edition 1 has the highest order, score = 100 \* 0.036 \* 2784 = 10,000
> > editionid 2 has the score = 100 \* 0.036 \* 71 = 255
> > editionid 3 has the score = 100 \* 0.036 \* 144 = 517
>
> > german user (with second language eng and third fre) searching for 'romeo
> > & shakespeare':
>
> > 1. editionid 5
> > because in his first language is ger, score = 100 \* 0.068 \* 325 = 2211
> > editionid 4 has the score = 5 \* 0.068 \* 1470 = 500
> > editionid 6 has the score = 3 \* 0.068 \* 487 = 99
>
> > spanish user (with second language german, third french, forth english)
> > searching for 'Shakespeare':
>
> > 1. edition 1
> > score = 2 \* 0.036 \* 2784 = 200
> > 2. edition 7
> > score = 2 \* 0.036 \* 1548 = 111
> > 3. edition 5
> > score = 5 \* 0.036 \* 325 = 58
>
> > I would be very happy if you could point me to the right direction since
> > this is all quite confusing for me so far.
> > Thanks a lot.
>
> > --
--
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:56am UTC](https://discuss.elastic.co/t/moving-the-search-from-postgresql-to-elasticsearch/10306/6 "2017-07-06T02:56:20Z")