-
Type:
Improvement
-
Status: Resolved
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.5
-
Fix Version/s: 5.4.2-HF21, 5.5.0-HF07, 5.6-RC1, 5.6
-
Component/s: Core VCS
-
Environment:PostgreSQL
-
Tags:
When searching on multiple fulltext field with an OR, the PosgreSQL query planner don't use the fulltext index.
This is a performance issue.
The reason is due to a PostgreSQL 9.1 bug that will be fixed in 9.2 but not backported in 9.1.
The work around is to rewrite the query from:
SELECT hierarchy.id FROM hierarchy JOIN fulltext ON fulltext.id = hierarchy.id, TO_TSQUERY('whatever1') query1, TO_TSQUERY('whatever2') query2 WHERE (query1 @@ nx_to_tsvector(fulltext.fulltext)) OR (query2 @@ nx_to_tsvector(fulltext.fulltext_title));
to
SELECT hierarchy.id FROM hierarchy JOIN fulltext ON fulltext.id = hierarchy.id WHERE (TO_TSQUERY('whatever1') @@ nx_to_tsvector(fulltext.fulltext)) OR (TO_TSQUERY('whatever2') @@ nx_to_tsvector(fulltext.fulltext_title));
