Function rank

Returns a float number that measures the strength of match between a text search vector and a text search query. Rank is typically used to sort matching search results.

rank([ weights FLOAT4[], ] tsv TSVECTOR, tsq TSQUERY [, normalization INT4]) RETURNS FLOAT4

• 'weights' is an array of 4 floats used to weight text search vector elements.
  • The default value of weights is '{0.1, 0.2, 0.4, 1.0} ' for text search vector elements marked with letters 'D', 'C', 'B', and 'A', respectively.
  • The values of weights must be between 0 and 1.
  • By default, text search vector elements not marked explicitly with a letter are treated as being marked with letter 'D'.
• 'tsv' is a text search vector to match against the text search query 'tsq'.
• 'normalization' is an integer that sets how the rank value is scaled by the length of the text search vector.
  • The default value '0' does not adjust the rank value.
  • A value of 1 divides the rank by the logarithm of the length of 'tsv'.
  • A value of 2 divides the rank by the length of 'tsv'.

SELECT set_curcfg('default_english');
 
 set_curcfg
------------

(1 row)

Rank without normalizing by length of ts_vec.

SELECT file, title, rank(ts_vec, to_tsquery('create & schema')) 
FROM postgresql_manual 
WHERE ts_vec @@ to_tsquery('create & schema') 
ORDER BY rank(ts_vec, to_tsquery('create & schema')) DESC 
LIMIT 5;
         
          file           |      title      |   rank
-------------------------+-----------------+----------
 sql-createschema.html   | CREATE SCHEMA   | 0.999717
 ddl-schemas.html        | Schemas         | 0.988608
 bookindex.html          | Index           | 0.971012
 sql-createtable.html    | CREATE TABLE    | 0.636038
 sql-createsequence.html | CREATE SEQUENCE | 0.627304
(5 rows)

Rank normalizing by logarithm of length of ts_vec.

SELECT file, title, rank(ts_vec, to_tsquery('create & schema'), 1) 
FROM postgresql_manual 
WHERE ts_vec @@ to_tsquery('create & schema') 
ORDER BY rank(ts_vec, to_tsquery('create & schema'), 1) DESC 
LIMIT 5;
          
	  file           |      title      |   rank
-------------------------+-----------------+-----------
 sql-createschema.html   | CREATE SCHEMA   |  0.164804
 ddl-schemas.html        | Schemas         |  0.138482
 bookindex.html          | Index           |  0.116201
 sql-createsequence.html | CREATE SEQUENCE | 0.0955284
 sql-grant.html          | GRANT           | 0.0864042
(5 rows)

Rank normalizing by length of ts_vec.

SELECT file, title, rank(ts_vec, to_tsquery('create & schema'), 2) 
FROM postgresql_manual 
WHERE ts_vec @@ to_tsquery('create & schema') 
ORDER BY rank(ts_vec, to_tsquery('create & schema'), 2) DESC 
LIMIT 5;
         
	 file          |     title     |    rank
-----------------------+---------------+------------
 sql-alterschema.html  | ALTER SCHEMA  | 0.00333546
 sql-createschema.html | CREATE SCHEMA | 0.00231953
 sql-dropschema.html   | DROP SCHEMA   | 0.00146315
 sql-createdomain.html | CREATE DOMAIN | 0.00117693
 sql-createview.html   | CREATE VIEW   | 0.00108162
(5 rows)

Rank using weights by letter.

ALTER TABLE postgresql_manual ADD COLUMN ts_vec_tb tsvector;

ALTER TABLE

UPDATE postgresql_manual 
SET ts_vec_tb = concat(setweight(to_tsvector(title), 'A'), setweight(to_tsvector(body), 'B'));

UPDATE 592

CREATE INDEX postgresql_manual_tb_in ON postgresql_manual USING gist(ts_vec_tb);

CREATE INDEX

vacuum full analyze;

VACUUM

Heavily weighting the title column over the body column.

SELECT file, title, rank('{0, 0, 0.01, 0.99}',ts_vec_tb, to_tsquery('create & aggregate')) 
FROM postgresql_manual 
WHERE ts_vec_tb @@ to_tsquery('create & aggregate') 
ORDER BY rank('{0, 0, 0.01, 0.99}', ts_vec_tb, to_tsquery('create & aggregate')) DESC 
LIMIT 5;

           file           |          title          |   rank
--------------------------+-------------------------+-----------
 sql-createaggregate.html | CREATE AGGREGATE        |  0.987949
 bookindex.html           | Index                   |  0.151227
 xaggr.html               | User-Defined Aggregates | 0.0588237
 reference.html           | Reference               | 0.0461835
 sql-commands.html        | SQL Commands            | 0.0461835
(5 rows)

Heavily weighting the body column over the title column.

SELECT file, title, rank('{0, 0, 0.99, 0.01}',ts_vec_tb, to_tsquery('create & aggregate')) 
FROM postgresql_manual 
WHERE ts_vec_tb @@ to_tsquery('create & aggregate') 
ORDER BY rank('{0, 0, 0.99, 0.01}', ts_vec_tb, to_tsquery('create & aggregate')) DESC 
LIMIT 5;
           
           file           |          title          |   rank
--------------------------+-------------------------+----------
 bookindex.html           | Index                   |        1
 xaggr.html               | User-Defined Aggregates |        1
 sql-createaggregate.html | CREATE AGGREGATE        |        1
 reference.html           | Reference               | 0.999998
 sql-commands.html        | SQL Commands            | 0.999998
(5 rows)