SQL Performance problem with like query after migration from MySQL to PostgreSQL

Question

I migrated my database from MySQL to PostgreSQL with pgloader, it's globally much more efficient but a query with like condition is more slower on PostgreSQL.

MySQL : ~1ms
PostgreSQL : ~110 ms

Table info:

105 columns
23 indexes
1.6M records

Columns info:

name character varying(30) COLLATE pg_catalog."default" NOT NULL,
ratemax3v3 integer NOT NULL DEFAULT 0,

Query is :

SELECT name, realm, region, class, id 
FROM personnages 
WHERE blacklisted = 0 AND name LIKE 'Krok%' AND region = 'eu'
ORDER BY ratemax3v3 DESC LIMIT 5;

EXPLAIN ANALYSE (PostgreSQL)

 Limit  (cost=629.10..629.12 rows=5 width=34) (actual time=111.128..111.130 rows=5 loops=1)
   ->  Sort  (cost=629.10..629.40 rows=117 width=34) (actual time=111.126..111.128 rows=5 loops=1)
         Sort Key: ratemax3v3 DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on personnages  (cost=9.63..627.16 rows=117 width=34) (actual time=110.619..111.093 rows=75 loops=1)
               Recheck Cond: ((name)::text ~~ 'Krok%'::text)
               Rows Removed by Index Recheck: 1
               Filter: ((blacklisted = 0) AND ((region)::text = 'eu'::text))
               Rows Removed by Filter: 13
               Heap Blocks: exact=88
               ->  Bitmap Index Scan on trgm_idx_name  (cost=0.00..9.60 rows=158 width=0) (actual time=110.581..110.582 rows=89 loops=1)
                     Index Cond: ((name)::text ~~ 'Krok%'::text)
 Planning Time: 0.268 ms
 Execution Time: 111.174 ms

Pgloader have been created indexes on ratemax3v3 and name like:

CREATE INDEX idx_24683_ratemax3v3
ON wow.personnages USING btree
(ratemax3v3 ASC NULLS LAST)
TABLESPACE pg_default;

CREATE INDEX idx_24683_name
ON wow.personnages USING btree
(name COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

I created a new index on name :

CREATE INDEX trgm_idx_name ON wow.personnages USING GIST (name gist_trgm_ops);

I'm quite a beginner with postgresql at the moment. Do you see anything I could do?

Don't hesitate to ask me if you need more information!

Antoine

105 columns 23 indexes <<-- looks suspect

wildplasser
– wildplasser

2021-02-22 10:14:58 +00:00
Commented Feb 22, 2021 at 10:14 — wildplasser
– wildplasser, Commented Feb 22, 2021 at 10:14
For comparison, what is the index and plan used in mysql?

jjanes
– jjanes

2021-02-22 16:03:00 +00:00
Commented Feb 22, 2021 at 16:03 — jjanes
– jjanes, Commented Feb 22, 2021 at 16:03

score 1 · Accepted Answer · 2021-02-22 10:22:51Z

1

To support a LIKE query like that (left anchored) you need to use a special "operator class":

CREATE INDEX ON wow.personnages(name varchar_pattern_ops);

But for your given query, an index on multiple columns would probably be more efficient:

CREATE INDEX ON wow.personnages(region, blacklisted, name varchar_pattern_ops);

Of maybe even a filtered index if e.g. the blacklisted = 0 is a static condition and there are relatively few rows matching that condition.

CREATE INDEX ON wow.personnages(region, name varchar_pattern_ops)
WHERE blacklisted = 0;

If the majority of the rows has blacklisted = 0 that won't really help (and adding the column to the index wouldn't help either). In that case just an index with (region, name varchar_pattern_ops) is probably more efficient.

edited Feb 22, 2021 at 10:22

answered Feb 22, 2021 at 10:11

user330315

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2021-02-22 10:12:00Z

0

If your pattern is anchored at the beginning, the following index would perform better:

CREATE INDEX ON personnages (name text_pattern_ops);

Besides, GIN indexes usually perform better than GiST indexes in a case like this. Try with a GIN index.

Finally, it is possible that the trigrams k, kr, kro, rok and ok occur very frequently, which would also make the index perform bad.

answered Feb 22, 2021 at 10:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

SQL Performance problem with like query after migration from MySQL to PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related