Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-15395

Very slow FTS on PostgreSQL

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Blocker Blocker
    • 3.2.10-RC1, 3.3.1-RC1
    • 3.2.0, 3.2.1, 3.2.2, 3.2.3, 3.2.4, 3.2.5, 3.2.6, 3.2.7, 3.2.8, 3.2.9, 3.3.0
    • Search
    • PHP 5.6/7.0/7.1/7.2/7.3/7.4 + PostgreSQL 9/10/11/12

      The engine makes an query

       

       ... WHERE (to_tsvector ('russian', p.post_subject || ' ' || p.post_text) @@ to_tsquery ('russian', 'searchtext')) ...

       

      but this concatination

       

      p.post_subject || ' ' || p.post_text

       

      disable an index at query and it takes a very long time

        

      Time: 44108.606 ms (00:44.109)

       

                                                                  QUERY PLAN
      ----------------------------------------------------------------------------------------------------------------------------------
       Gather  (cost=1000.00..418610.81 rows=10231 width=649)
         Workers Planned: 2
         ->  Parallel Seq Scan on phpbb_posts p  (cost=0.00..416587.71 rows=4263 width=649)
               Filter: (to_tsvector('russian'::regconfig, (((post_subject)::text || ' '::text) || post_text)) @@ '''searchtext'''::tsquery)
      
      

      IMO the query must be like this

       

      WHERE (to_tsvector ('russian', p.post_subject) @@ to_tsquery ('russian', 'searchtext')) OR (to_tsvector ('russian', p.post_text) @@ to_tsquery ('russian', 'searchtext'))
      

      Result:

      Time: 9.850 ms

                                                                                                 QUERY PLAN
       
      ---------------------------------------------------------------------------------------------------------------------------------------
      ----------------------------------------------------------
       Bitmap Heap Scan on phpbb_posts p  (cost=4952.65..25172.24 rows=5080 width=649)
         Recheck Cond: ((to_tsvector('russian'::regconfig, (post_subject)::text) @@ '''searchtext'''::tsquery) OR (to_tsvector('russian':
      :regconfig, post_text) @@ '''searchtext'''::tsquery))
         ->  BitmapOr  (cost=4952.65..4952.65 rows=5081 width=0)
               ->  Bitmap Index Scan on phpbb_posts_russian_post_subject  (cost=0.00..4427.33 rows=443 width=0)
                     Index Cond: (to_tsvector('russian'::regconfig, (post_subject)::text) @@ '''searchtext'''::tsquery)
               ->  Bitmap Index Scan on phpbb_posts_russian_post_text  (cost=0.00..522.79 rows=4638 width=0)
                     Index Cond: (to_tsvector('russian'::regconfig, post_text) @@ '''searchtext'''::tsquery)
      
      

       
       In my patch I'm using the old code from phpbb 3.0 ( https://area51.phpbb.com/phpBB/viewtopic.php?t=28707 )

       

       

        1. fulltext_postgres_patch.diff
          2 kB
          msk123 [X]
        2. fulltext_postgres.diff
          1 kB
          DragonKidou [X]

            Marc Marc
            msk123 msk123 [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: