smlar - PolarDB - Alibaba Cloud Documentation Center

In e-commerce and search engine scenarios, computing similarity for large-scale datasets is a critical technical challenge. Simple similarity implementations are slow and resource-intensive. smlar is an open-source third-party extension for PolarDB for PostgreSQL that provides functions for efficient similarity computation within the database, along with similarity operators that support GiST and GIN indexes. The smlar extension supports all built-in PostgreSQL data types.

Prerequisites

The following versions of PolarDB for PostgreSQL are supported:

PostgreSQL 16 (minor version 2.0.16.9.6.0 or later)
PostgreSQL 14 (minor version 2.0.14.5.1.0 or later)
PostgreSQL 11 (minor version 2.0.11.9.28.0 or later)

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。

Usage

Install the extension.
```
CREATE EXTENSION smlar;
```
Note
The smlar extension conflicts with the rum extension on the % operator. Therefore, these two extensions cannot be created in the same schema.
Run the following commands to calculate the similarity between two arrays.
- ```
SELECT smlar('{3,2}'::int[], '{3,2,1}');
```
  The following result is returned:
```
  smlar
----------
 0.816497
(1 row)
```
- ```
SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / (N.a + N.b)' );
```
  The following result is returned:
```
  smlar
----------
 0.333333
(1 row)
```
  Note
  For more information about other functions, see Functions and operators.
Uninstall the extension.
```
DROP EXTENSION smlar;
```

Functions and operators

Function or operator	Description
float4 smlar(anyarray, anyarray)	Calculates the similarity between two arrays of the same data type. The arrays must have the same data type.
float4 smlar(anyarray, anyarray, bool useIntersect)	Calculates the similarity between two arrays of a custom composite type (element, weight). The useIntersect parameter specifies whether only overlapping elements or all elements participate in the computation. Note The composite type is defined as follows: `CREATE TYPE type_name AS (element_name anytype, weight_name FLOAT4);`
float4 smlar(anyarray a, anyarray b, text formula)	Calculates the similarity between two arrays of the same data type by using a custom formula. The arrays must have the same data type. The predefined variables of the formula are as follows: N.i: the number of identical elements in the two arrays (intersection). N.a: the number of unique elements in the first array. N.b: the number of unique elements in the second array.
anyarray % anyarray	Returns TRUE if the similarity of the two arrays exceeds the threshold. Otherwise, returns FALSE.
text[] tsvector2textarray(tsvector)	Converts a tsvector value to a text array.
anyarray array_unique(anyarray)	Sorts the array and removes duplicate elements.
float4 inarray(anyarray, anyelement)	Returns 1.0 if the element is found in the array. Otherwise, returns 0.
float4 inarray(anyarray, anyelement, float4, float4)	Returns the third argument if the element is found in the array. Otherwise, returns the fourth argument.

Configurable parameters

Parameter	Description
smlar.threshold FLOAT	The similarity threshold used by the percent sign (%) operator to determine whether two arrays are similar.
smlar.persistent_cache BOOL	Specifies whether the cache for global statistics is stored in memory that is independent of transactions.
smlar.type STRING	The similarity formula. Valid similarity types: cosine (default), tfidf, and overlap.
smlar.stattable STRING	The name of the table that stores collection-level statistics. The table is defined as follows: `CREATE TABLE table_name ( value data_type UNIQUE, ndoc int4 (or bigint) NOT NULL CHECK (ndoc>0) );`
smlar.tf_method STRING	The method for calculating term frequency (TF). Valid values: n: simple count (default). log: `1 + log(n)`. const: frequency equals 1.
smlar.idf_plus_one BOOL	The method for calculating inverse document frequency (IDF). Valid values: FALSE (default): `log(d / df)`. TRUE: `log(1 + d / df)`.

References

For more information about the smlar extension, see: