Feature #5219
implement native full-text search for word index and CONTAINS support
0%
Related issues
History
#1 Updated by Eric Faulhaber over 5 years ago
- Subject changed from improve word index and CONTAINS performance to further word index and CONTAINS improvements
With #1587, we have provided first class support for the word index and CONTAINS operator, including parsing/indexing the content of word indexed columns, better sorting for results of CONTAINS-based queries, and improving the performance significantly over the previous, UDF-based approach.
This task serves as a placeholder for further work to improve the performance of CONTAINS queries and word indexed fields and any other refinements to word index and CONTAINS support.
The exploration and possible use of database native support to implement CONTAINS support is considered part of this effort, unless other improvements can be devised to bring the performance of the current implementation more in line with that of the 4GL's implementation.
We also need to review edit 3821c/12004, which changed the index selection portion of query conversion to ignore word indices. I am concerned this change may have had unwanted side effects to downstream conversion, besides removing the unwanted effect on the ORDER BY clause of the target query.
#2 Updated by Igor Skornyakov over 5 years ago
Eric Faulhaber wrote:
We also need to review edit 3821c/12004, which changed the index selection portion of query conversion to ignore word indices. I am concerned this change may have had unwanted side effects to downstream conversion, besides removing the unwanted effect on the ORDER BY clause of the target query.
Actuallу, after recent changes regarding sorting restoring the logic with word indices selection, should be easy. Moreover, it can make the logic for deciding if the word table should be selected for implicit sorting more consistent.
#4 Updated by Greg Shah almost 2 years ago
The exploration and possible use of database native support to implement CONTAINS support is considered part of this effort, unless other improvements can be devised to bring the performance of the current implementation more in line with that of the 4GL's implementation.
I think we should test native full text search now, to see if it can eliminate the performance bottlenecks (e.g. #8454) we are finding in our implementation (SQL UDFs + special tables for each index). Is the current implementation well encapsulated so that a prototype could be "plugged in"?
#5 Updated by Greg Shah about 2 months ago
- topics Word Indexes added
#6 Updated by Greg Shah 8 days ago
- Subject changed from further word index and CONTAINS improvements to implement native full-text search for word index and CONTAINS support
- Parent task deleted (
#1585)
The idea of this task is to leverage the native database full-text-search capability. It could be implemented for one database (e.g. PostgreSQL) and hidden in the dialect support so that the existing cross-database approach (our own word tables that we maintain with functions) is still available for any database that doesn't have its native approach implemented (or which doesn't have suitable FTS capability).
For some early discussion about this idea, please see #1587-37 and #1587-41 through #1587-51.
Considering tasks like #11616 and #11298, I think it is time for a prototype of the PostgeSQL native FTS support to see how it performs.
#9 Updated by Greg Shah 8 days ago
- Related to Feature #1587: implement full support for word indexes added