Question 90825 in PostgreSQL Question posted in
The official documentation can be found here.

Queries using LIKE are exceptionally slow – Postgresql

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?
Tags:

2

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search