Date: Wed, 5 Nov 2025 09:18:06 +0100 (CET)
From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@lists.postgresql.org
Message-ID: 
 <VisenaEmail.4.25b9c8bc175afa23.19a530e9ec5@origo-test01.app.internal.visena.net>
Subject: trgm and index-usage when using similarity function
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_231756_1120951262.1762330686943"
Archived-At: 
 <https://www.postgresql.org/message-id/VisenaEmail.4.25b9c8bc175afa23.19a530e9ec5%40origo-test01.app.internal.visena.net>
Precedence: bulk

------=_Part_231756_1120951262.1762330686943
Content-Type: multipart/related;
	boundary="----=_Part_231757_1812023446.1762330686943"

------=_Part_231757_1812023446.1762330686943
Content-Type: multipart/alternative;
	boundary="----=_Part_231758_1465039356.1762330686970"

------=_Part_231758_1465039356.1762330686970
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable


Using PG-18.0, I'm having problems with trgm and index-usage when using=20
similarity-function.


Given the following schema:

CREATE TABLE person
(
entity_id BIGSERIAL PRIMARY KEY,
firstname text,
lastname text,
fullname_search text,
birthdate date
);

INSERT INTO person
SELECT pers.entity_id, pers.firstname, pers.lastname, pers.fullname_search,=
=20
pers.birthdate
FROM onp_crm_person pers;

CREATE INDEX test_idx_onp_crm_person_duplicates
ON person USING gin (fullname_search gin_trgm_ops)
;


This query uses index and is fast, but I have to set=20
pg_trgm.similarity_threshold manually first:

SET pg_trgm.similarity_threshold =3D 0.9;

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.entity_id, p.firstname, p.lastname, p.fullname_search, p.birthdate
FROM person p
WHERE EXISTS (
SELECT *
FROM person d
 WHERE d.fullname_search % p.fullname_search
AND d.entity_id <> p.entity_id
LIMIT 1
)
;

=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82 QUERY PLAN =E2=94=82

=E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4
=E2=94=82 Nested Loop Semi Join (cost=3D2.19..319906.05 rows=3D256 width=3D=
45) (actual=20
time=3D29.527..2626.465 rows=3D7497.00 loops=3D1) =E2=94=82
=E2=94=82 Buffers: shared hit=3D972919 =E2=94=82
=E2=94=82 -> Seq Scan on person p (cost=3D0.00..488.81 rows=3D25581 width=
=3D45) (actual=20
time=3D0.024..1.127 rows=3D25581.00 loops=3D1) =E2=94=82
=E2=94=82 Buffers: shared hit=3D233 =E2=94=82
=E2=94=82 -> Bitmap Heap Scan on person d (cost=3D2.19..10.03 rows=3D256 wi=
dth=3D25) (actual=20
time=3D0.102..0.102 rows=3D0.29 loops=3D25581) =E2=94=82
=E2=94=82 Recheck Cond: (fullname_search % p.fullname_search) =E2=94=82
=E2=94=82 Rows Removed by Index Recheck: 0 =E2=94=82
=E2=94=82 Filter: (entity_id <> p.entity_id) =E2=94=82
=E2=94=82 Rows Removed by Filter: 1 =E2=94=82
=E2=94=82 Heap Blocks: exact=3D34252 =E2=94=82
=E2=94=82 Buffers: shared hit=3D972686 =E2=94=82
=E2=94=82 -> Bitmap Index Scan on test_idx_onp_crm_person_duplicates (cost=
=3D0.00..2.13=20
rows=3D256 width=3D0) (actual time=3D0.097..0.097 rows=3D1.99 loops=3D25581=
) =E2=94=82
=E2=94=82 Index Cond: (fullname_search % p.fullname_search) =E2=94=82
=E2=94=82 Index Searches: 25581 =E2=94=82
=E2=94=82 Buffers: shared hit=3D933853 =E2=94=82
=E2=94=82 Planning: =E2=94=82
=E2=94=82 Buffers: shared hit=3D2 =E2=94=82
=E2=94=82 Planning Time: 3.620 ms =E2=94=82
=E2=94=82 JIT: =E2=94=82
=E2=94=82 Functions: 7 =E2=94=82
=E2=94=82 Options: Inlining false, Optimization false, Expressions true, De=
forming=20
true =E2=94=82
=E2=94=82 Timing: Generation 0.772 ms (Deform 0.230 ms), Inlining 0.000 ms,=
=20
Optimization 5.522 ms, Emission 23.219 ms, Total 29.513 ms =E2=94=82
=E2=94=82 Execution Time: 2628.643 ms =E2=94=82

=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
(23 rows)


But using similarity-function:

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.entity_id, p.firstname, p.lastname, p.fullname_search, p.birthdate
FROM person p
WHERE EXISTS (
SELECT *
FROM person d
 WHERE similarity(d.fullname_search, p.fullname_search) >=3D 0.9
AND d.entity_id <> p.entity_id
LIMIT 1
)
;
=E2=80=A6 this has been running for 30 minutes now and I've cancelled it.


Any suggestions on how to fix this query so I can:

 *=20
specify the similarity-factor as part of the query

 *=20
Make it fast

Thanks.


--
Andreas Joseph Krogh
------=_Part_231758_1465039356.1762330686970
Content-Type: text/html;charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<style class=3D"visena-ck-5-styles">
.ck-content {
    --ck-color-image-caption-background: #f7f7f7;
    --ck-color-image-caption-text: #333333;
    --ck-color-mention-background: #990030E6;
    --ck-color-mention-text: #990030;
    --ck-color-table-caption-background: #f7f7f7;
    --ck-color-table-caption-text: #333333;
    --ck-highlight-marker-blue: #72ccfd;
    --ck-highlight-marker-green: #62f962;
    --ck-highlight-marker-pink: #fc7899;
    --ck-highlight-marker-yellow: #fdfd77;
    --ck-highlight-pen-green: #128a00;
    --ck-highlight-pen-red: #e71313;
    --ck-image-style-spacing: 1.5em;
    --ck-spacing-large: 2px;
    --ck-inline-image-style-spacing: calc(var(--ck-image-style-spacing) / 2=
);
    --ck-todo-list-checkmark-size: 16px;
    /*
    This works when email is READ in Visena-reader, because there the conte=
nt of the email is in "shadow-DOM",
     which has it's own :root, but needs to be declared in ".ck-content p, =
.ck-content div" as well for the actual
     CKEditor to display it correctly, else it get's its default from .lift=
Template

    */
    font-family: Arial, Helvetica, sans-serif;
    font-size: 14px;
}

/* ckeditor5-image/theme/imageresize.css */
.ck-content .image.image_resized {
    max-width: 100%;
    display: block;
    box-sizing: border-box;
}
/* ckeditor5-image/theme/imageresize.css */
.ck-content .image.image_resized img {
    width: 100%;
    height: auto !important;
}
/* ckeditor5-image/theme/imageresize.css */
.ck-content .image.image_resized > figcaption {
    display: block;
}
/* ckeditor5-image/theme/imagecaption.css */
.ck-content .image > figcaption {
    display: table-caption;
    caption-side: bottom;
    word-break: break-word;
    color: var(--ck-color-image-caption-text);
    background-color: var(--ck-color-image-caption-background);
    padding: .6em;
    font-size: .75em;
    outline-offset: -1px;
}

/* ckeditor5-font/theme/fontsize.css */
/* THIS IS A VISENA SPECIFIC CONFIG */
.ck-content p, .ck-content div {
    font-family: Arial, Helvetica, sans-serif;
    font-size: 14px;
}

.ck-content p.visenaNormal {
    margin: 0;
}

/* ckeditor5-font/theme/fontsize.css */
.ck-content .text-tiny {
    font-size: .7em;
}
/* ckeditor5-font/theme/fontsize.css */
.ck-content .text-small {
    font-size: .85em;
}
/* ckeditor5-font/theme/fontsize.css */
.ck-content .text-big {
    font-size: 1.4em;
}
/* ckeditor5-font/theme/fontsize.css */
.ck-content .text-huge {
    font-size: 1.8em;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-block-align-left,
.ck-content .image-style-block-align-right {
    max-width: calc(100% - var(--ck-image-style-spacing));
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-align-left,
.ck-content .image-style-align-right {
    clear: none;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-side {
    float: right;
    margin-left: var(--ck-image-style-spacing);
    max-width: 50%;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-align-left {
    float: left;
    margin-right: var(--ck-image-style-spacing);
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-align-center {
    margin-left: auto;
    margin-right: auto;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-align-right {
    float: right;
    margin-left: var(--ck-image-style-spacing);
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-block-align-right {
    margin-right: 0;
    margin-left: auto;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-style-block-align-left {
    margin-left: 0;
    margin-right: auto;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content p + .image-style-align-left,
.ck-content p + .image-style-align-right,
.ck-content p + .image-style-side {
    margin-top: 0;
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-inline.image-style-align-left,
.ck-content .image-inline.image-style-align-right {
    margin-top: var(--ck-inline-image-style-spacing);
    margin-bottom: var(--ck-inline-image-style-spacing);
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-inline.image-style-align-left {
    margin-right: var(--ck-inline-image-style-spacing);
}
/* ckeditor5-image/theme/imagestyle.css */
.ck-content .image-inline.image-style-align-right {
    margin-left: var(--ck-inline-image-style-spacing);
}
/* ckeditor5-image/theme/image.css */
.ck-content .image {
    clear: both;
    text-align: center;
    min-width: 50px;
    display: inline-flex;
    max-width: 100%;
    align-items: flex-start;
}
/* ckeditor5-image/theme/image.css */
.ck-content .image img {
    display: block;
    margin: 0 auto;
    max-width: 100%;
    min-width: 100%;
}
.ck-content img {
    height: auto !important;
}
/* ckeditor5-image/theme/image.css */
.ck-content .image-inline {
    /*
     * Normally, the .image-inline would have "display: inline-block" and "=
img { width: 100% }" (to follow the wrapper while resizing).;
     * Unfortunately, together with "srcset", it gets automatically stretch=
ed up to the width of the editing root.
     * This strange behavior does not happen with inline-flex.
     */
    display: inline-flex;
    max-width: 100%;
    align-items: flex-start;
}
/* ckeditor5-image/theme/image.css */
.ck-content .image-inline picture {
    display: flex;
}
/* ckeditor5-image/theme/image.css */
.ck-content .image-inline picture,
.ck-content .image-inline img {
    flex-grow: 1;
    flex-shrink: 1;
    max-width: 100%;
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .marker-yellow {
    background-color: var(--ck-highlight-marker-yellow);
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .marker-green {
    background-color: var(--ck-highlight-marker-green);
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .marker-pink {
    background-color: var(--ck-highlight-marker-pink);
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .marker-blue {
    background-color: var(--ck-highlight-marker-blue);
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .pen-red {
    color: var(--ck-highlight-pen-red);
    background-color: transparent;
}
/* ckeditor5-highlight/theme/highlight.css */
.ck-content .pen-green {
    color: var(--ck-highlight-pen-green);
    background-color: transparent;
}
/* ckeditor5-horizontal-line/theme/horizontalline.css */
.ck-content hr {
    margin: 15px 0;
    height: 4px;
    background: #dedede;
    border: 0;
}
/* ckeditor5-block-quote/theme/blockquote.css */
.ck-content blockquote {
    overflow: hidden;
    padding-right: 0;
    padding-left: 1ex;
    margin-left: 0;
    margin-right: 0;
    font-style: unset !important;
    border-left: solid 1px #cccccc !important;
}
/* ckeditor5-table/theme/blockquote.css */
/* THIS IS A VISENA SPECIFIC CONFIG */
.ck-content .blockquote {
    font-style: unset;
}
/* ckeditor5-block-quote/theme/blockquote.css */
.ck-content[dir=3D"rtl"] blockquote {
    border-left: 0;
    border-right: solid 1px #cccccc;
}
/* ckeditor5-basic-styles/theme/code.css */
.ck-content code {
    background-color: #c7c7c7;
    padding: 0 1px;
    font-size: small;
    border-radius: 2px;
}
/* ckeditor5-table/theme/tablecaption.css */
.ck-content .table > figcaption {
    display: table-caption;
    caption-side: top;
    word-break: break-word;
    text-align: center;
    color: var(--ck-color-table-caption-text);
    background-color: var(--ck-color-table-caption-background);
    padding: .6em;
    font-size: .75em;
    outline-offset: -1px;
}
/* ckeditor5-table/theme/table.css */
.ck-content .table {
    margin: 0 auto;
    display: table;
}
/* ckeditor5-table/theme/table.css */
.ck-content .table table {
    border-collapse: collapse;
    border-spacing: 0;
    width: 100%;
    height: 100%;
    border: 1px double #b3b3b3;
}
/* ckeditor5-table/theme/table.css */
.ck-content .table table td,
.ck-content .table table th {
    min-width: 2em;
    padding: .4em;
    border: 1px solid #bfbfbf;
}
/* ckeditor5-table/theme/table.css */
.ck-content .table table th {
    font-weight: bold;
    background: #000000E6;
}
/* ckeditor5-table/theme/table.css */
.ck-content[dir=3D"rtl"] .table th {
    text-align: right;
}
/* ckeditor5-table/theme/table.css */
.ck-content[dir=3D"ltr"] .table th {
    text-align: left;
}

/*Visena specific*/
.ck-content .table {
    margin-left: 0 !important;
    /*float: left;*/
}

/* ckeditor5-page-break/theme/pagebreak.css */
.ck-content .page-break {
    position: relative;
    clear: both;
    padding: 5px 0;
    display: flex;
    align-items: center;
    justify-content: center;
}
/* ckeditor5-page-break/theme/pagebreak.css */
.ck-content .page-break::after {
    content: '';
    position: absolute;
    border-bottom: 2px dashed #c4c4c4;
    width: 100%;
}
/* ckeditor5-page-break/theme/pagebreak.css */
.ck-content .page-break__label {
    position: relative;
    z-index: 1;
    padding: .3em .6em;
    display: block;
    text-transform: uppercase;
    border: 1px solid #c4c4c4;
    border-radius: 2px;
    font-family: Arial, Helvetica, sans-serif;
    font-size: 0.75em;
    font-weight: bold;
    color: #333333;
    background: #ffffff;
    box-shadow: 2px 2px 1px #000000;
    -webkit-user-select: none;
    -moz-user-select: none;
    -ms-user-select: none;
    user-select: none;
}
/* ckeditor5-media-embed/theme/mediaembed.css */
.ck-content .media {
    clear: both;
    margin: 0 0;
    display: block;
    min-width: 15em;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list {
    list-style: none;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list li {
    margin-bottom: 5px;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list li .todo-list {
    margin-top: 5px;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label > input {
    -webkit-appearance: none;
    display: inline-block;
    position: relative;
    width: var(--ck-todo-list-checkmark-size);
    height: var(--ck-todo-list-checkmark-size);
    vertical-align: middle;
    border: 0;
    left: -25px;
    margin-right: -15px;
    right: 0;
    margin-left: 0;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label > input::before {
    display: block;
    position: absolute;
    box-sizing: border-box;
    content: '';
    width: 100%;
    height: 100%;
    border: 1px solid #333333;
    border-radius: 2px;
    transition: 250ms ease-in-out box-shadow, 250ms ease-in-out background,=
 250ms ease-in-out border;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label > input::after {
    display: block;
    position: absolute;
    box-sizing: content-box;
    pointer-events: none;
    content: '';
    left: calc( var(--ck-todo-list-checkmark-size) / 3 );
    top: calc( var(--ck-todo-list-checkmark-size) / 5.3 );
    width: calc( var(--ck-todo-list-checkmark-size) / 5.3 );
    height: calc( var(--ck-todo-list-checkmark-size) / 2.6 );
    border-style: solid;
    border-color: transparent;
    border-width: 0 calc( var(--ck-todo-list-checkmark-size) / 8 ) calc( va=
r(--ck-todo-list-checkmark-size) / 8 ) 0;
    transform: rotate(45deg);
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label > input[checked]::before {
    background: #26ab33;
    border-color: #26ab33;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label > input[checked]::after {
    border-color: #ffffff;
}
/* ckeditor5-list/theme/todolist.css */
.ck-content .todo-list .todo-list__label .todo-list__label__description {
    vertical-align: middle;
}
/* ckeditor5-code-block/theme/codeblock.css */
.ck-content pre {
    padding: 1em;
    color: #353535;
    background: #c7c7c7;
    border: 1px solid #c4c4c4;
    border-radius: 2px;
    text-align: left;
    direction: ltr;
    tab-size: 4;
    white-space: pre-wrap;
    font-style: normal;
    min-width: 200px;
}
/* ckeditor5-code-block/theme/codeblock.css */
.ck-content pre code {
    background: unset;
    padding: 0;
    border-radius: 0;
}
/* ckeditor5-mention/theme/mention.css */
.ck-content .mention {
    background: var(--ck-color-mention-background);
    color: var(--ck-color-mention-text);
}
@media print {
    /* ckeditor5-page-break/theme/pagebreak.css */
    .ck-content .page-break {
        padding: 0;
    }
    /* ckeditor5-page-break/theme/pagebreak.css */
    .ck-content .page-break::after {
        display: none;
    }
}
.ck-content figure.image {
    margin-left: 0;
}
.ck-content .image.image-style-block-align-right {
    margin-left: auto;
    margin-right: 0;
    float: none !important;
    display: table !important;
}
.ck-content .image.image-style-align-center {
    margin-left: auto;
    float: none !important;
    display: table !important;
}

p.MsoNormal, li.MsoNormal, div.MsoNormal {
    margin: 0;
}</style><div class=3D"ck-content" data-visena-message-id=3D"_VisenaEmail.4=
.25b9c8bc175afa23.19a530e9ec5@origo-test01.app.internal.visena.net_"><p cla=
ss=3D"visenaNormal">Using PG-18.0, I'm having problems with trgm and index-=
usage when using <code>similarity</code>-function.</p><p class=3D"visenaNor=
mal">=C2=A0</p><p class=3D"visenaNormal">Given the following schema:</p><di=
v style=3D"background-color:#ffffff;color:#080808;"><pre style=3D"font-fami=
ly:'JetBrains Mono',monospace;font-size:9.8pt;"><span style=3D"color:#00008=
0;"><strong>CREATE TABLE </strong></span><span style=3D"color:#7f0000;"><st=
rong>person</strong></span><br>(<br>    <span style=3D"color:#871094;">enti=
ty_id   </span><span style=3D"color:#000080;"><strong>BIGSERIAL PRIMARY KEY=
</strong></span>,<br>    <span style=3D"color:#871094;">firstname   </span>=
<span style=3D"color:#000080;"><strong>text</strong></span>,<br>    <span s=
tyle=3D"color:#871094;">lastname    </span><span style=3D"color:#000080;"><=
strong>text</strong></span>,<br>    <span style=3D"color:#871094;">fullname=
_search </span><span style=3D"color:#000080;"><strong>text</strong></span>,=
<br>    <span style=3D"color:#871094;">birthdate   </span><span style=3D"co=
lor:#000080;"><strong>date</strong></span><br>);<br><br><span style=3D"colo=
r:#000080;"><strong>INSERT INTO </strong></span><span style=3D"color:#7f000=
0;"><strong>person</strong></span><br><span style=3D"color:#000080;"><stron=
g>SELECT </strong></span>pers.<span style=3D"color:#871094;">entity_id</spa=
n>, pers.<span style=3D"color:#871094;">firstname</span>, pers.<span style=
=3D"color:#871094;">lastname</span>, pers.<span style=3D"color:#871094;">fu=
llname_search</span>, pers.<span style=3D"color:#871094;">birthdate</span><=
br><span style=3D"color:#000080;"><strong>FROM </strong></span><span style=
=3D"color:#7f0000;"><strong>onp_crm_person </strong></span>pers;<br><br><sp=
an style=3D"color:#000080;"><strong>CREATE INDEX </strong></span>test_idx_o=
np_crm_person_duplicates<br>    <span style=3D"color:#000080;"><strong>ON <=
/strong></span><span style=3D"color:#7f0000;"><strong>person </strong></spa=
n><span style=3D"color:#000080;"><strong>USING </strong></span><span style=
=3D"color:#7f0000;"><strong>gin </strong></span>(<span style=3D"color:#8710=
94;">fullname_search </span><span style=3D"color:#7f0000;"><strong>gin_trgm=
_ops</strong></span>)<br>;</pre></div><p class=3D"visenaNormal">=C2=A0</p><=
p class=3D"visenaNormal">This query uses index and is fast, but I have to s=
et <code>pg_trgm.similarity_threshold</code> manually first:</p><div style=
=3D"background-color:#ffffff;color:#080808;"><pre style=3D"font-family:'Jet=
Brains Mono',monospace;font-size:9.8pt;"><span style=3D"color:#000080;"><st=
rong>SET </strong></span><span style=3D"color:#7f0000;"><strong>pg_trgm</st=
rong></span>.<span style=3D"color:#7f0000;"><strong>similarity_threshold </=
strong></span>=3D <span style=3D"color:#1750eb;">0.9</span>;<br><br><span s=
tyle=3D"color:#000080;"><strong>EXPLAIN </strong></span>(<span style=3D"col=
or:#000080;"><strong>ANALYZE</strong></span>, <span style=3D"color:#000080;=
"><strong>BUFFERS</strong></span>)<br><span style=3D"color:#000080;"><stron=
g>SELECT </strong></span>p.<span style=3D"color:#871094;">entity_id</span>,=
 p.<span style=3D"color:#871094;">firstname</span>, p.<span style=3D"color:=
#871094;">lastname</span>, p.<span style=3D"color:#871094;">fullname_search=
</span>, p.<span style=3D"color:#871094;">birthdate</span><br><span style=
=3D"color:#000080;"><strong>FROM </strong></span><span style=3D"color:#7f00=
00;"><strong>person </strong></span>p<br><span style=3D"color:#000080;"><st=
rong>WHERE </strong></span><span style=3D"color:#00627a;"><i>EXISTS </i></s=
pan>(<br>    <span style=3D"color:#000080;"><strong>SELECT </strong></span>=
<i>*</i><br><span style=3D"color:#1750eb;">    </span><span style=3D"color:=
#000080;"><strong>FROM </strong></span><span style=3D"color:#7f0000;"><stro=
ng>person </strong></span><span style=3D"color:#000080;"><strong>d</strong>=
</span><br><span style=3D"color:#000080;"><strong>    WHERE d</strong></spa=
n>.<span style=3D"color:#871094;">fullname_search </span>% p.<span style=3D=
"color:#660e7a;"><strong>fullname_search</strong></span><br><span style=3D"=
color:#660e7a;"><strong>      </strong></span><span style=3D"color:#000080;=
"><strong>AND d</strong></span>.<span style=3D"color:#871094;">entity_id </=
span>&lt;&gt; p.<span style=3D"color:#660e7a;"><strong>entity_id</strong></=
span><br><span style=3D"color:#660e7a;"><strong>    </strong></span><span s=
tyle=3D"color:#000080;"><strong>LIMIT </strong></span><span style=3D"color:=
#1750eb;">1</span><br>)<br>;<br>=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=90<br>=E2=94=82                                                  =
                      QUERY PLAN                                           =
                             =E2=94=82<br>=E2=94=9C=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=A4<br>=E2=94=82 Nested Loop Semi Join  (cost=3D2.19..319=
906.05 rows=3D256 width=3D45) (actual time=3D29.527..2626.465 rows=3D7497.0=
0 loops=3D1)                                      =E2=94=82<br>=E2=94=82   =
Buffers: shared hit=3D972919                                               =
                                                                           =
   =E2=94=82<br>=E2=94=82   -&gt;  Seq Scan on person p  (cost=3D0.00..488.=
81 rows=3D25581 width=3D45) (actual time=3D0.024..1.127 rows=3D25581.00 loo=
ps=3D1)                                     =E2=94=82<br>=E2=94=82         =
Buffers: shared hit=3D233                                                  =
                                                                        =E2=
=94=82<br>=E2=94=82   -&gt;  Bitmap Heap Scan on person d  (cost=3D2.19..10=
.03 rows=3D256 width=3D25) (actual time=3D0.102..0.102 rows=3D0.29 loops=3D=
25581)                                =E2=94=82<br>=E2=94=82         Rechec=
k Cond: (fullname_search % p.fullname_search)                              =
                                                                =E2=94=82<b=
r>=E2=94=82         Rows Removed by Index Recheck: 0                       =
                                                                           =
               =E2=94=82<br>=E2=94=82         Filter: (entity_id &lt;&gt; p=
.entity_id)                                                                =
                                               =E2=94=82<br>=E2=94=82      =
   Rows Removed by Filter: 1                                               =
                                                                         =
=E2=94=82<br>=E2=94=82         Heap Blocks: exact=3D34252                  =
                                                                           =
                            =E2=94=82<br>=E2=94=82         Buffers: shared =
hit=3D972686                                                               =
                                                        =E2=94=82<br>=E2=94=
=82         -&gt;  Bitmap Index Scan on test_idx_onp_crm_person_duplicates =
 (cost=3D0.00..2.13 rows=3D256 width=3D0) (actual time=3D0.097..0.097 rows=
=3D1.99 loops=3D25581) =E2=94=82<br>=E2=94=82               Index Cond: (fu=
llname_search % p.fullname_search)                                         =
                                                 =E2=94=82<br>=E2=94=82    =
           Index Searches: 25581                                           =
                                                                           =
=E2=94=82<br>=E2=94=82               Buffers: shared hit=3D933853          =
                                                                           =
                            =E2=94=82<br>=E2=94=82 Planning:               =
                                                                           =
                                                      =E2=94=82<br>=E2=94=
=82   Buffers: shared hit=3D2                                              =
                                                                           =
         =E2=94=82<br>=E2=94=82 Planning Time: 3.620 ms                    =
                                                                           =
                                   =E2=94=82<br>=E2=94=82 JIT:             =
                                                                           =
                                                             =E2=94=82<br>=
=E2=94=82   Functions: 7                                                   =
                                                                           =
             =E2=94=82<br>=E2=94=82   Options: Inlining false, Optimization=
 false, Expressions true, Deforming true                                   =
                                       =E2=94=82<br>=E2=94=82   Timing: Gen=
eration 0.772 ms (Deform 0.230 ms), Inlining 0.000 ms, Optimization 5.522 m=
s, Emission 23.219 ms, Total 29.513 ms                           =E2=94=82<=
br>=E2=94=82 Execution Time: 2628.643 ms                                   =
                                                                           =
                =E2=94=82<br>=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=98<br>(23 rows)</pre><p class=3D"visenaNormal">=C2=A0</p></div><p cl=
ass=3D"visenaNormal">But using <code>similarity</code>-function:</p><div st=
yle=3D"background-color:#ffffff;color:#080808;"><pre style=3D"font-family:'=
JetBrains Mono',monospace;font-size:9.8pt;"><span style=3D"color:#000080;">=
<strong>EXPLAIN </strong></span>(<span style=3D"color:#000080;"><strong>ANA=
LYZE</strong></span>, <span style=3D"color:#000080;"><strong>BUFFERS</stron=
g></span>)<br><span style=3D"color:#000080;"><strong>SELECT </strong></span=
>p.<span style=3D"color:#871094;">entity_id</span>, p.<span style=3D"color:=
#871094;">firstname</span>, p.<span style=3D"color:#871094;">lastname</span=
>, p.<span style=3D"color:#871094;">fullname_search</span>, p.<span style=
=3D"color:#871094;">birthdate</span><br><span style=3D"color:#000080;"><str=
ong>FROM </strong></span><span style=3D"color:#7f0000;"><strong>person </st=
rong></span>p<br><span style=3D"color:#000080;"><strong>WHERE </strong></sp=
an><span style=3D"color:#00627a;"><i>EXISTS </i></span>(<br>    <span style=
=3D"color:#000080;"><strong>SELECT </strong></span><i>*</i><br><i>    </i><=
span style=3D"color:#000080;"><strong>FROM </strong></span><span style=3D"c=
olor:#7f0000;"><strong>person </strong></span><span style=3D"color:#000080;=
"><strong>d</strong></span><br><span style=3D"color:#000080;"><strong>    W=
HERE </strong></span><span style=3D"color:#00627a;"><i>similarity</i></span=
>(<span style=3D"color:#000080;"><strong>d</strong></span>.<span style=3D"c=
olor:#871094;">fullname_search</span>, p.<span style=3D"color:#660e7a;"><st=
rong>fullname_search</strong></span>) &gt;=3D <span style=3D"color:#1750eb;=
">0.9</span><br><span style=3D"color:#1750eb;">      </span><span style=3D"=
color:#000080;"><strong>AND d</strong></span>.<span style=3D"color:#871094;=
">entity_id </span>&lt;&gt; p.<span style=3D"color:#660e7a;"><strong>entity=
_id</strong></span><br><span style=3D"color:#660e7a;"><strong>    </strong>=
</span><span style=3D"color:#000080;"><strong>LIMIT </strong></span><span s=
tyle=3D"color:#1750eb;">1</span><br>)<br>;</pre></div><p class=3D"visenaNor=
mal">=E2=80=A6 this has been running for 30 minutes now and I've cancelled =
it.</p><p class=3D"visenaNormal">=C2=A0</p><p class=3D"visenaNormal">Any su=
ggestions on how to fix this query so I can:</p><ol><li><p class=3D"visenaN=
ormal">specify the similarity-factor as part of the query</p></li><li><p cl=
ass=3D"visenaNormal">Make it fast</p></li></ol><p class=3D"visenaNormal">Th=
anks.</p><p class=3D"visenaNormal">=C2=A0</p><div class=3D"visena-email-sig=
nature" data-visena-message-id=3D"_VisenaEmail.4.25b9c8bc175afa23.19a530e9e=
c5@origo-test01.app.internal.visena.net_"><div><div><div>--</div><div><span=
 style=3D"font-family:arial,helvetica,sans-serif;">Andreas Joseph Krogh</sp=
an></div></div></div></div></div>
------=_Part_231758_1465039356.1762330686970--

------=_Part_231757_1812023446.1762330686943--

------=_Part_231756_1120951262.1762330686943--