Re: pg_trgm word_similarity query does not use index for input strings longer than 8 characters

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