smlar

smlar is an open-source extension for PolarDB for PostgreSQL (Compatible with Oracle) that calculates array similarity using GiST and GIN indexes. It supports all built-in PostgreSQL data types, making it suited for similarity search in applications such as e-commerce recommendations and search engines.

Install the extension

CREATE EXTENSION smlar;

Note

The % operator of smlar conflicts with that of rum. Do not create both extensions in the same schema.

Calculate similarity

The smlar() function returns a float4 value representing the similarity between two arrays.

Basic similarity between two arrays of the same type:

SELECT smlar('{3,2}'::int[], '{3,2,1}');
  smlar
----------
 0.816497
(1 row)

Similarity using a custom formula:

SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / (N.a + N.b)');
  smlar
----------
 0.333333
(1 row)

For the full list of functions and operators, see Functions and operators.

Remove the extension

DROP EXTENSION smlar;

Functions and operators

Function or operator	Returns	Description
`smlar(anyarray, anyarray)`	`float4`	Calculates the similarity of two arrays of the same data type.
`smlar(anyarray, anyarray, bool useIntersect)`	`float4`	Calculates the similarity of two arrays of custom composite types (elements and weights). Set `useIntersect` to `true` to use only overlapping elements; set to `false` to use all elements.
`smlar(anyarray a, anyarray b, text formula)`	`float4`	Calculates similarity using a custom formula. Available variables: `N.i` (number of identical elements), `N.a` (unique elements in the first array), `N.b` (unique elements in the second array).
`anyarray % anyarray`	`bool`	Returns `true` if the similarity of the two arrays exceeds `smlar.threshold`; `false` otherwise.
`tsvector2textarray(tsvector)`	`text[]`	Converts a `tsvector` array to a string array.
`array_unique(anyarray)`	`anyarray`	Sorts and deduplicates the array.
`inarray(anyarray, anyelement)`	`float4`	Returns `1.0` if the element exists in the array; `0` otherwise.
`inarray(anyarray, anyelement, float4, float4)`	`float4`	Returns the third parameter if the element exists in the array; the fourth parameter otherwise.

To define a composite type for use with the second smlar() overload:

CREATE TYPE type_name AS (element_name anytype, weight_name FLOAT4);

Parameters

Parameter	Type	Default	Description
`smlar.threshold`	FLOAT	—	Similarity threshold for the `%` operator. Two arrays are considered similar if their similarity score exceeds this value.
`smlar.persistent_cache`	BOOL	—	When set to `true`, stores cached global statistics in transaction-independent memory.
`smlar.type`	STRING	`cosine`	The formula used to calculate similarity. Valid values: `cosine`, `tfidf`, `overlap`.
`smlar.stattable`	STRING	—	The name of the table storing collection range statistics. See the table definition below.
`smlar.tf_method`	STRING	`n`	The method for calculating term frequency (TF). Valid values: `n` (simple count), `log` (1 + log(n)), `const` (1).
`smlar.idf_plus_one`	BOOL	`false`	The method for calculating inverse document frequency (IDF). Set to `false` to use log(d/df); set to `true` to use log(1 + d/df).

To use TF-IDF similarity, create a statistics table with the following structure:

CREATE TABLE table_name (
  value   data_type UNIQUE,
  ndoc    int4 (or bigint)  NOT NULL CHECK (ndoc > 0)
);

Then set smlar.stattable to the name of this table.

Install the extension

Calculate similarity

Remove the extension

Functions and operators

Parameters

References