Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
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
-
PHP 5.6/7.0/7.1/7.2/7.3/7.4 + PostgreSQL 9/10/11/12
Description
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 )