FWD » Core Development » Database

This task is deferred from #9370-13 and most of the description and investigation was already delivered there.

TL;DR: the word index support generates SQL queries that extract parts of the contains string into CTEs (named WCTEs). If the clause if something like f1 CONTAINS "a&b&c", it generates 3 WCTEs (one computing the records that have a, the second with b and the last with c) so that the where clause will use recid in (select ... from WCTE) kind of components.

Each part of the conjunction can be written as a WCTE with more complex where clauses (e.g. (a|b)&(c|d)&(e|f). But in practice, there are several cases where a WCTE ends up being very trivial:

with wcte1 as (
    select distinct t.recid
    from test t
        join test__word as w1 on (w1.parent__id = t.recid and (w1.word = UPPER(('test'))))
)

and the where clause:

and (test.recid in (select recid from wcte1)) 

For such pattern, we can simply emit: (test.recid in (select recid from test__word as w1 where (w1.parent__id = test.recid and (w1.word = UPPER(('test')))))) in the where clause. This is optimal in PostgreSQL, because it uses a Nested Loop Semijoin which is reasonably fast.

• a large performance test suite to ensure that the inlining is actually optimal.
• implementation

Stefanel, please describe here the optimization in #10431-12 here. We may want to implement such optimization directly in FWD run-time where possible.

Alexandru Lungu wrote:

Stefanel, please describe here the optimization in #10431-12 here. We may want to implement such optimization directly in FWD run-time where possible.

This optimization is already implemented in trunk (using a simple join with the CTE, without an extra WHERE recid IN (SELECT recid FROM cte)).
Is enabled only under certain conditions, and only if the WHERE clause is simple and consists only of ANDs.