From: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-performance@jhacker.de
To: pgsql-performance@lists.postgresql.org
Subject: Re: pg_trgm word_similarity query does not use index for input strings longer than 8 characters
Date: Tue, 07 Dec 2021 04:10:20 +0100
Message-ID: <9b1caa63c39ff31e5d6c22bb8575368743433028.camel@cybertec.at> (raw)
In-Reply-To: <e76c242b-2d78-813f-2f95-5ba5c0bf3b5c@mailbox.org>
References: <e76c242b-2d78-813f-2f95-5ba5c0bf3b5c@mailbox.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, pgsql-performance@jhacker.de, pgsql-performance@lists.postgresql.org
Subject: Re: pg_trgm word_similarity query does not use index for input strings longer than 8 characters
In-Reply-To: <9b1caa63c39ff31e5d6c22bb8575368743433028.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox