From: | Greg Navis <contact(at)gregnavis(dot)com> |
---|---|
To: | Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [pg_trgm] Making similarity(?, ?) < ? use an index |
Date: | 2016-06-09 08:57:11 |
Message-ID: | CAA6WWt-uDJv5oUvuOC0b69S6=m0=_H+i15rg_nJRUYLQNDpCuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Artur, no worries, I'm not writing any code ;-)
I did the following:
CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC);
CREATE OR REPLACE FUNCTION trgm_check_match (string TEXT, match trgm_match)
RETURNS bool
AS 'SELECT match.match <-> string <= 1 - match.threshold'
LANGUAGE SQL;
CREATE OPERATOR %(leftarg = text, rightarg = trgm_match,
procedure=trgm_check_match);
This allows me to write:
SELECT ('Warsaw' % row('Warsw', 0.3)::trgm_match);
I'm not sure how to make this operator use an index. It seems I need to
create an operator class but I'm not sure how. This is how pg_trgm creates
its operator class:
-- create the operator class for gist
CREATE OPERATOR CLASS gist_trgm_ops
FOR TYPE text USING gist
AS
OPERATOR 1 % (text, text),
FUNCTION 1 gtrgm_consistent (internal, text, smallint,
oid, internal),
FUNCTION 2 gtrgm_union (internal, internal),
FUNCTION 3 gtrgm_compress (internal),
FUNCTION 4 gtrgm_decompress (internal),
FUNCTION 5 gtrgm_penalty (internal, internal,
internal),
FUNCTION 6 gtrgm_picksplit (internal, internal),
FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal),
STORAGE gtrgm;
Should my operator class mimic the one above?
--
Greg Navis
I help tech companies to scale Heroku-hosted Rails apps.
Free, biweekly scalability newsletter for SaaS CEOs
<http://www.gregnavis.com/newsletter/>
From | Date | Subject | |
---|---|---|---|
Next Message | Sunil N Shinde | 2016-06-09 10:01:57 | Re: High availability and load balancing ... |
Previous Message | Andrew Beverley | 2016-06-09 06:10:34 | Optimise OR condiditions across multiple joins |