CODEWITHSUNDEEP
postgresqlperformancequery-optimizationsql-like
Touchard Antoine
Touchard Antoine

Reputation: 59

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

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.

Table info:

Columns info:

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

Upvotes: 0

Views: 246

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246092

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.

Upvotes: 0

Related Questions