Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
On Wed, Mar 7, 2012 at 11:59 AM, Andrey Chursin 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
There is some good news coming from Oleg Bartunov and Alexander Korotkov about improving ranking speed: http://wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf 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 wrote: > In a previous discussion thread, Oleg suggested that ts_rank is unable to > use GIN indices: > http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php > > This is the only information I have about this. >
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
In a previous discussion thread, Oleg suggested that ts_rank is unable to use GIN indices: http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php This is the only information I have about this. On Wed, Mar 7, 2012 at 18:59, Andrey Chursin 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: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
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 написал: > 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
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 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*
[GENERAL] Full text search ranking: ordering using index and proximiti ranking with OR queries
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
