From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | pgsql-performance(at)jhacker(dot)de, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_trgm word_similarity query does not use index for input strings longer than 8 characters |
Date: | 2021-12-07 03:10:20 |
Message-ID: | 9b1caa63c39ff31e5d6c22bb8575368743433028.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, 2021-11-30 at 22:38 +0100, pgsql-performance(at)jhacker(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-12-07 17:08:05 | Re: pg_trgm word_similarity query does not use index for input strings longer than 8 characters |
Previous Message | Justin Pryzby | 2021-12-04 19:53:20 | Re: An I/O error occurred while sending to the backend (PG 13.4) |