Postgres Pro
Downloads
Home   >   mailing lists

Thread: Full text search ranking: ordering using index and proximiti ranking with OR queries

Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Andrey Chursin
Date:
Hello

I have two unrelated questions about fts function ts_rank:

1) I've created GiST index on column with fts vector, but query
SELECT * FROM table ORDER BY ts_rank(field, :query) LIMIT 20
is perfomed with sequential table scan. Index was created on field
column. Does it mean FTS indexes does not support order by ranking? Or
I need somehow to create separated index for ranking?

2) I have a misunderstanding with proximity ranking work. Given two
vectors 'a:1 b:2' and 'a:1 b:1000', i am measuring ts_rank(vector, 'a'
| 'b'). And it is equal! But when i am replacing query with &
operator, e.g. asking for ts_rank(vector, 'a' & 'b') i am getting
different numbers. Why do I get proximity ranking only for AND fts
queries? This is a problem as far as to_tsquery produces OR queries,
so i need self-written postprocessing of query to replace OR with AND.

--
Regards,
Andrey

Re: Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Nicolas Grilly
Date:
According to Oleg in a previous discussion, ts_rank does not use index because index does not store enough information for ranking:
http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php

On Sat, Feb 18, 2012 at 12:39, Andrey Chursin <andll@danasoft.ws> wrote:
Hello

I have two unrelated questions about fts function ts_rank:

1) I've created GiST index on column with fts vector, but query
SELECT * FROM table ORDER BY ts_rank(field, :query) LIMIT 20
is perfomed with sequential table scan. Index was created on field
column. Does it mean FTS indexes does not support order by ranking? Or
I need somehow to create separated index for ranking?

2) I have a misunderstanding with proximity ranking work. Given two
vectors 'a:1 b:2' and 'a:1 b:1000', i am measuring ts_rank(vector, 'a'
| 'b'). And it is equal! But when i am replacing query with &
operator, e.g. asking for ts_rank(vector, 'a' & 'b') i am getting
different numbers. Why do I get proximity ranking only for AND fts
queries? This is a problem as far as to_tsquery produces OR queries,
so i need self-written postprocessing of query to replace OR with AND.

--
Regards,
Andrey

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Nicolas Grilly
Garden / Vocation City
+33 1 45 72 48 78 - office
+33 6 03 00 25 34 - mobile
www.gardentechno.com - Développement web & reporting / Web development & data analytics
www.vocationcity.com - Plateforme de recrutement sur le web / Web recruitment platform

Re: Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Andrey Chursin
Date:
Is there any way to sort by ranking, avoiding seq scan?
The only way i see now is to use pg_trgm instead of ts_rank, but we
did not check yet how applicable is it for our purposes.

7 марта 2012 г. 20:53 пользователь Nicolas Grilly
<nicolas@gardentechno.com> написал:
> According to Oleg in a previous discussion, ts_rank does not use index
> because index does not store enough information for ranking:
> http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php
>
> On Sat, Feb 18, 2012 at 12:39, Andrey Chursin <andll@danasoft.ws> wrote:
>>
>> Hello
>>
>> I have two unrelated questions about fts function ts_rank:
>>
>> 1) I've created GiST index on column with fts vector, but query
>> SELECT * FROM table ORDER BY ts_rank(field, :query) LIMIT 20
>> is perfomed with sequential table scan. Index was created on field
>> column. Does it mean FTS indexes does not support order by ranking? Or
>> I need somehow to create separated index for ranking?
>>
>> 2) I have a misunderstanding with proximity ranking work. Given two
>> vectors 'a:1 b:2' and 'a:1 b:1000', i am measuring ts_rank(vector, 'a'
>> | 'b'). And it is equal! But when i am replacing query with &
>> operator, e.g. asking for ts_rank(vector, 'a' & 'b') i am getting
>> different numbers. Why do I get proximity ranking only for AND fts
>> queries? This is a problem as far as to_tsquery produces OR queries,
>> so i need self-written postprocessing of query to replace OR with AND.
>>
>> --
>> Regards,
>> Andrey
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> --
> Nicolas Grilly
> Garden / Vocation City
> +33 1 45 72 48 78 - office
> +33 6 03 00 25 34 - mobile
> www.gardentechno.com - Développement web & reporting / Web development &
> data analytics
> www.vocationcity.com - Plateforme de recrutement sur le web / Web
> recruitment platform



--
Regards,
Andrey

Re: Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Nicolas Grilly
Date:
In a previous discussion thread, Oleg suggested that ts_rank is unable to use GIN indices:

This is the only information I have about this.

On Wed, Mar 7, 2012 at 18:59, Andrey Chursin <andll@danasoft.ws> wrote:
Is there any way to sort by ranking, avoiding seq scan?
The only way i see now is to use pg_trgm instead of ts_rank, but we
did not check yet how applicable is it for our purposes.

Re: Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Nicolas Grilly
Date:
There is some good news coming from Oleg Bartunov and Alexander Korotkov about improving ranking speed:

It's worth reading their slides to gain a better understanding of PostgreSQL fulltext internals.


On Wed, Mar 7, 2012 at 8:05 PM, Nicolas Grilly <nicolas@gardentechno.com> wrote:
In a previous discussion thread, Oleg suggested that ts_rank is unable to use GIN indices:

This is the only information I have about this.

Re: Full text search ranking: ordering using index and proximiti ranking with OR queries

From
Merlin Moncure
Date:
On Wed, Mar 7, 2012 at 11:59 AM, Andrey Chursin <andll@danasoft.ws> wrote:
> Is there any way to sort by ranking, avoiding seq scan?
> The only way i see now is to use pg_trgm instead of ts_rank, but we
> did not check yet how applicable is it for our purposes.

pg_tgrm works very well in terms of measuring similarity between two
ascii strings...many non-english languages will struggle.  I doubt
(although I ever tried) it's useful for matching a small phrase to a
large document.

merlin