Hello,
recently I wrote a query that provides suggestions from a Postgres table.
It should be able to work despite smaller typos and thus I chose to use
the pg_trgm extension (https://www.postgresql.org/docs/current/pgtrgm.html).
When measuring the performance, I observed great differences in the
query time, depending on the input string.
Analysis showed that Postgres sometimes used the created indexes and
sometimes it didn't, even though it would provide a considerable speedup.
In the included test case the degradation occurs for all input strings
of length 8 or longer, for shorter strings the index is used.
My questions:
Why doesn't the query planner choose to use the index?
Can I make Postgres use the index, and if so, how?
I understand that trying to outsmart the planner is generally a bad
idea. Maybe the query can be rewritten or there are some parameters that
could be tweaked.
## Setup Information
Hardware: Intel i5-8250U, 8GB RAM, encrypted SSD, no RAID
$ uname -a
Linux 5.11.0-40-generic #44~20.04.2-Ubuntu SMP Tue Oct 26 18:07:44 UTC
2021 x86_64 x86_64 x86_64 GNU/Linux
Software:
OS: Ubuntu 20.04
Postgres: PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110,
64-bit
The Postgres docker image was used.
Docker: Docker version 20.10.5, build 55c4c88
Image used: postgres:14.1
Configuration:
The config file was not changed.
name | current_setting | source
----------------------------+--------------------+----------------------
application_name | psql | client
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
enable_seqscan | off | session
lc_messages | en_US.utf8 | configuration file
lc_monetary | en_US.utf8 | configuration file
lc_numeric | en_US.utf8 | configuration file
lc_time | en_US.utf8 | configuration file
listen_addresses | * | configuration file
log_timezone | Etc/UTC | configuration file
max_connections | 100 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_size | 1GB | configuration file
min_wal_size | 80MB | configuration file
shared_buffers | 128MB | configuration file
TimeZone | Etc/UTC | configuration file
## Test Case
The test case creates a simple table and fills it with 10000 identical
entries.
The query is executed twice with an 8 character string, once with
sequential scans enabled, and once with sequential scans disabled.
The first query does not use the index, even if the second query shows
that it would be much faster.
docker run --name postgres -e POSTGRES_PASSWORD=postgres -d postgres:14.1
docker exec -it postgres bash
psql -U postgres
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')
;
## Additional Test Case Info
Schemata:
Table "public.song"
Column | Type | Collation | Nullable | Default |
Storage | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
artist | character varying(20) | | | |
extended | | |
title | character varying(20) | | | |
extended | | |
Indexes:
"artist_trgm" gin (artist gin_trgm_ops)
"title_trgm" gin (title gin_trgm_ops)
Access method: heap
Index "public.artist_trgm"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
artist | integer | yes | artist | plain |
gin, for table "public.song"
Index "public.title_trgm"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
title | integer | yes | title | plain |
gin, for table "public.song"
Table Metadata:
postgres=# SELECT relname, relpages, reltuples, relallvisible, relkind,
relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class
WHERE relname='song';
relname | relpages | reltuples | relallvisible | relkind | relnatts |
relhassubclass | reloptions | pg_table_size
---------+----------+-----------+---------------+---------+----------+----------------+------------+---------------
song | 55 | 10000 | 55 | r | 2 |
f | | 483328
EXPLAIN ANALYZE of the "slow" query
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on song (cost=0.00..205.00 rows=1 width=13) (actual
time=68.896..68.897 rows=0 loops=1)
Filter: (((artist)::text %> '12345678'::text) OR ((title)::text %>
'12345678'::text))
Rows Removed by Filter: 10000
Planning Time: 0.304 ms
Execution Time: 68.928 ms
EXPLAIN ANALYZE of the "fast" query
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on song (cost=288.00..292.02 rows=1 width=13)
(actual time=0.023..0.024 rows=0 loops=1)
Recheck Cond: (((artist)::text %> '12345678'::text) OR
((title)::text %> '12345678'::text))
-> BitmapOr (cost=288.00..288.00 rows=1 width=0) (actual
time=0.022..0.023 rows=0 loops=1)
-> Bitmap Index Scan on artist_trgm (cost=0.00..144.00
rows=1 width=0) (actual time=0.013..0.014 rows=0 loops=1)
Index Cond: ((artist)::text %> '12345678'::text)
-> Bitmap Index Scan on title_trgm (cost=0.00..144.00 rows=1
width=0) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: ((title)::text %> '12345678'::text)
Planning Time: 0.224 ms
Execution Time: 0.052 ms
The behaviour is identical when using similarity instead of word_similarity.
GIN indexes were chosen because the table is queried far more often than
it is updated.
I tried increasing shared_buffers, effective_cache_size or work_mem to
no avail.
Any help would be greatly appreciated.
Regards
Jonathan
