smlar

smlar : Effective similarity search

Overview

Packages

Source

pig build pkg smlar;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install smlar;		# install via package name, for the active PG version

pig install smlar -v 18;   # install for PG 18
pig install smlar -v 17;   # install for PG 17
pig install smlar -v 16;   # install for PG 16
pig install smlar -v 15;   # install for PG 15
pig install smlar -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION smlar;

Usage

smlar: Effective similarity search for PostgreSQL arrays. Source: README

The smlar extension provides effective similarity search on PostgreSQL arrays using configurable similarity formulas, GiST and GIN index support, and TF/IDF weighting.

Functions

float4 smlar(anyarray, anyarray)

Computes similarity of two arrays. Arrays should be the same type.

float4 smlar(anyarray, anyarray, bool useIntersect)

Computes similarity of two arrays of composite types. Composite type looks like:

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

The useIntersect option points to use only intersected elements in the denominator.

float4 smlar(anyarray a, anyarray b, text formula)

Computes similarity of two arrays by a given formula. Predefined variables in formula:

• N.i – number of common elements in both arrays (intersection)
• N.a – number of unique elements in first array
• N.b – number of unique elements in second array

Example:

SELECT smlar('{1,4,6}'::int[], '{5,4,6}');
SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / sqrt(N.a * N.b)');
-- These two calls are equivalent.

anyarray % anyarray

Returns true if similarity of the arrays is greater than the threshold limit.

text[] tsvector2textarray(tsvector)

Transforms tsvector type to text array.

anyarray array_unique(anyarray)

Sort and unique array.

float4 inarray(anyarray, anyelement)

Returns zero if second argument does not present in the first one and 1.0 in opposite case.

float4 inarray(anyarray, anyelement, float4, float4)

Returns fourth argument if second argument does not present in the first one and third argument in opposite case.

GUC Configuration Variables

smlar.threshold  FLOAT

Arrays with similarity lower than threshold are not similar by % operation.

smlar.persistent_cache  BOOL

Cache of global stat is stored in transaction-independent memory.

smlar.type  STRING

Type of similarity formula: cosine (default), tfidf, overlap.

smlar.stattable  STRING

Name of table storing set-wide statistic. Table should be defined as:

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

A row with null value means total number of documents. Used only for smlar.type = 'tfidf'.

smlar.tf_method  STRING

Calculation method for term frequency. Values:

• "n" – simple counting of entries (default)
• "log" – 1 + log(n)
• "const" – TF is equal to 1

Used only for smlar.type = 'tfidf'.

smlar.idf_plus_one  BOOL

If false (default), calculate idf as log(d/df). If true, as log(1+d/df). Used only for smlar.type = 'tfidf'.

It is highly recommended to add to postgresql.conf:

smlar.threshold = 0.6  # or any other value > 0 and < 1

GiST/GIN Index Support

The % and && operations are supported with GiST and GIN indexes for many array types: