1

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;

Query Plan

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:

  1. Why are LIKE queries this much slower than = queries?
  2. 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?
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
robbieperry22
  • 1,753
  • 1
  • 18
  • 49
  • 2
    Share text as text, not as images. Especially not as images in which the text gets cut off at a certain point. – jjanes Nov 23 '22 at 02:48
  • 2
    To compare the speed of two queries, it would help to see the plans for both queries, not just one of them. – jjanes Nov 23 '22 at 02:50
  • The execution plan is better shared as [formatted text](http://stackoverflow.com/help/formatting) 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 https://explain.depesz.com/ –  Nov 23 '22 at 07:17

2 Answers2

3

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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
SQLpro
  • 3,994
  • 1
  • 6
  • 14