On Tue, 2021-11-30 at 22:38 +0100, pgsql-performance@xxxxxxxxxx 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
