Message-ID: <9b1caa63c39ff31e5d6c22bb8575368743433028.camel@cybertec.at>
Subject: Re: pg_trgm word_similarity query does not use index for input
 strings longer than 8 characters
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-performance@jhacker.de, pgsql-performance@lists.postgresql.org
Date: Tue, 07 Dec 2021 04:10:20 +0100
In-Reply-To: <e76c242b-2d78-813f-2f95-5ba5c0bf3b5c@mailbox.org>
References: <e76c242b-2d78-813f-2f95-5ba5c0bf3b5c@mailbox.org>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-2.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9b1caa63c39ff31e5d6c22bb8575368743433028.camel%40cybertec.at>
Precedence: bulk

On Tue, 2021-11-30 at 22:38 +0100, pgsql-performance@jhacker.de wrote:
> ## Setup Information
> Hardware: Intel i5-8250U, 8GB RAM, encrypted SSD, no RAID
> [...]
>
> Configuration:
> The config file was not changed.
> [...]
>
> ## Test Case
> [...]
> CREATE EXTENSION pg_trgm;
> 
> CREATE TABLE song (
>      artist      varchar(20),
>      title       varchar(20)
> );
> 
> INSERT INTO song (artist, title)
> SELECT 'artist','title'
> FROM generate_series(1,10000);
> 
> CREATE INDEX artist_trgm ON song USING GIN (artist gin_trgm_ops);
> CREATE INDEX title_trgm ON song USING GIN (title gin_trgm_ops);
> 
> -- Tips from https://wiki.postgresql.org/wiki/Slow_Query_Questions
> ANALYZE;
> VACUUM;
> REINDEX TABLE song;
> 
> \set query '12345678'
> 
> -- This query is slow
> EXPLAIN ANALYZE
> SELECT song.artist, song.title
> FROM song
> WHERE (song.artist %> :'query' OR song.title %> :'query')
> ;
> 
> set enable_seqscan=off;
> 
> -- This query is fast
> EXPLAIN ANALYZE
> SELECT song.artist, song.title
> FROM song
> WHERE (song.artist %> :'query' OR song.title %> :'query')
> ;

The table is quite small; with a bigger table, the test would be more meaningful.

Since you have SSDs, you should tune "random_page_cost = 1.1".
This makes the planner prefer index scans, and it leads to the index scan
being chosen in your case.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com