0

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 !!!

2
  • "I have about 50 million forums and each forum has over 30 million topics" You have over 1.5 quadrillion rows?
    – jjanes
    Jun 23 '21 at 15:21
  • Yeah ... but not like that (each forum has between 1 and 30 million topics) ! It's something that I generated by default to test heavy data handling ... Jun 23 '21 at 17:01
2

varchar_pattern_ops with a btree index only works for patterns which do not start with a wildcard. So not for ilike '%PHP%', which does start with a wildcard.

Multicolumn GIN indexes are not like multicolumn btree indexes. They don't chain columns together efficiently. They are more like two single-column indexes, which to be used here would have to be combined with a BitmapAnd. Which might not be bad, but would still be less efficient than the btree-style chaining.

This may be a case where it would make sense to try a GIST trigram index rather than GIN trigram. Multicolumn GIST indexes do chain columns together better than GIN. Although they are not as efficient for doing trigram searches, so you would have to try it and see if the better chaining overcomes the worse efficiency.

As for why it would change when you change the constant, you should show us the full EXPLAIN (ANALYZE, BUFFERS) for each query, as well as for the queries which take each part of the WHERE clause in isolation, and let us know of any non-default configuration settings you have.

0

Use trigram indexes.

create extension btree_gin;

create extension pg_trgm;

create index idx_forum_topic on forum_topic using gin (forum,subject gin_trgm_ops);
2
  • i did, but there is a problem. Sometimes PostgreSQL not uses this index !! for example i executed this query SELECT * FROM "forum_topic" WHERE "forum"=2 AND "subject" ILIKE '%mmap2%' LIMIT 2; and the result is 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 and it takes 26 seconds ! Jun 23 '21 at 9:36
  • Postgres thinks sequential scan will be faster than index. Execute set enable_seqscan = off; try again if this forces index scan and speeds up your query. Jun 23 '21 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.