Queries using LIKE are exceptionally slow

Question

I have a database with over 30,000,000 entries. When performing queries (including an ORDER BY clause) on a text field, the = operator results in relatively fast results. However we have noticed that when using the LIKE operator, the query becomes remarkably slow, taking minutes to complete. For example:

SELECT * FROM work_item_summary WHERE manager LIKE '%manager' ORDER BY created;

Creating indices on the keywords being searched will of course greatly speed up the query. The problem is we must support queries on any arbitrary pattern, and on any column, making this solution not viable.

My questions are:

Why are LIKE queries this much slower than = queries?
Is there any other way these generic queries can be optimized, or is about as good as one can get for a database with so many entries?

Share text as text, not as images. Especially not as images in which the text gets cut off at a certain point. — jjanes
– jjanes, Commented Nov 23, 2022 at 2:48
To compare the speed of two queries, it would help to see the plans for both queries, not just one of them. — jjanes
– jjanes, Commented Nov 23, 2022 at 2:50
The execution plan is better shared as formatted text and preserving the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. You can also upload the plan to explain.depesz.com — user330315
– user330315, Commented Nov 23, 2022 at 7:17

Erwin Brandstetter · Accepted Answer · 2022-11-23 02:48:46Z

5

Your query plan shows a sequential scan, which is slow for big tables, and also not surprising since your LIKE pattern has a leading wildcard that cannot be supported with a plain B-tree index.

You need to add index support. Either a trigram GIN index to support any and all patterns, or a COLLATE "C" B-tree expression index on the reversed string to specifically target leading wildcards.

See:

answered Nov 23, 2022 at 2:48

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

SQLpro · Accepted Answer · 2022-11-24 17:50:15Z

One technic to speed up queries that search partial word (eg '%something%') si to use rotational indexing technic wich is not implementedin most of RDBMS.

This technique consists of cutting out each word of a sentence and then cutting it in "rotation", ie creating a list of parts of this word from which the first letter is successively removed.

As an example the word "electricity" will be exploded into 10 words :

lectricity

ectricity

ctricity

tricity

ricity

icity

city

ity

ty

y

Then you put all those words into a dictionnary that is a simple table with an index... and reference the root word.

Tables for this are :

CREATE TABLE T_WRD
(WRD_ID                BIGINT IDENTITY PRIMARY KEY,
 WRD_WORD              VARCHAR(64) NOT NULL UNIQUE,
 WRD_DROW              GENERATED ALWAYS AS (REVERSE(WRD_WORD) NOT NULL UNIQUE) ;
GO
    
CREATE TABLE T_WORD_ROTATE_STRING_WRS
(WRD_ID                BIGINT NOT NULL REFERENCES T_WRD (WRD_ID),
 WRS_ROTATE            SMALLINT NOT NULL,
 WRD_ID_PART           BIGINT NOT NULL REFERENCES T_WRD (WRD_ID),
 PRIMARY KEY (WRD_ID,  WRS_ROTATE));
GO

Collectives™ on Stack Overflow

Queries using LIKE are exceptionally slow

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related