PostgreSQL btree index for int with character varying (ILIKE) not working

0 votes

2 answers

1329 views

                          Hello I have about 50 million forums and each forum has over 30 million topics which this is my topic structure in PostgreSQL

    CREATE TABLE public.forum_topic
    (
        "forum" integer NOT NULL,
        "user" integer NOT NULL,
        "submit" integer NOT NULL,
        "subject" character varying(64) NOT NULL,
        "content" character varying(8192) NOT NULL,
        "ip" integer NOT NULL,
        PRIMARY KEY ("forum", "user", "submit")
    );

This is my search query, find every topic with keyword 'PHP' in it (in its subject) in forum 1000 (forum ID)

    SELECT * FROM "forum_topic" WHERE "forum"=1000 AND "subject" ILIKE '%PHP%' LIMIT 10

I know that i must create an index for this kind of search, and I did.

    CREATE INDEX forum_topic_forum_subject_idx
        ON public.forum_topic USING btree
        (forum, subject varchar_pattern_ops);

but when I executed the select query (top), there is no sign of using 'forum_topic_forum_subject_idx' index that I created, and it takes 10 seconds to execute it  !!!!!

    Seq Scan on forum_topic as forum_topic (rows=0 loops=5)
    Filter: (((subject)::text ~~* '%PHP%'::text) AND (forum = 1000))
    Rows Removed by Filter: 9998001

Do you have any idea for my situation? Do you have a better method for this kind of index?

I'm using PostgreSQL 13

**---- Update ----**

I created a gin index:

    create index idx_forum_topic on forum_topic using gin (forum,subject gin_trgm_ops);

but something's wired happened ! I executed 2 queries and one is executed based on the 'idx_forum_topic' index and one is executed based on 'seq scan' !!!!

    SELECT * FROM "forum_topic" WHERE "forum"=26854 AND "subject" ILIKE '%mmap2%' LIMIT 2;

result (1 second):

    Bitmap Heap Scan on forum_topic as forum_topic (rows=1 loops=1)
    Recheck Cond: ((forum = 26854) AND ((subject)::text ~~* '%mmap2%'::text))
    Heap Blocks: exact=1

and

    SELECT * FROM "forum_topic" WHERE "forum"=2 AND "subject" ILIKE '%mmap2%' LIMIT 2;

result (27 seconds)

    Seq Scan on forum_topic as forum_topic (rows=1 loops=1)
    Filter: (((subject)::text ~~* '%mmap2%'::text) AND (forum = 2))
    Rows Removed by Filter: 49990003

!!! What is the problem !!! 
                        

Asked by HelloMachine (101 rep)

Jun 23, 2021, 08:49 AM
Last activity: Jun 23, 2021, 03:56 PM

PostgreSQL btree index for int with character varying (ILIKE) not working

Related Questions