optimizer regression for indirect queries on Lucene full-text and range indexes in eXist-2.2 and eXist-3.0.RC1

[rvdb]

In eXist-2.2 and eXist-3.3.RC1, the optimizer seems to behave buggy for indirect queries on node sets that have been stored in a variable first. When such indirect queries are run on the Lucene full-text and range indexes, the search is either slow or incorrect, which suggests that the optimizer isn't working optimally.

For comparison, behaviour for both 'direct' and 'indirect' versions of queries will be described below, on both Lucene full-text and range indexes. These tests can be run straight on the eXist demo app.

• Lucene full-text index: indirect queries work, but take much longer than direct queries

  • direct query: 419 results in under 0.020 sec.

    let $a := //SPEECH[ft:query(., 'hamlet')] return $a

  • indirect query: 419 results in more than 2 sec.

    let $a := //SPEECH return $a[ft:query(., 'hamlet')]

• Lucene range index: indirect queries don't return any results at all

  • direct query: 359 results in 0.018 sec.

    let $a := //SPEAKER[range:matches(., 'HAMLET')] return $a

  • indirect query: 0 results

    let $a := //SPEAKER return $a[range:matches(., 'HAMLET')]

It seems that for indirect queries on the Lucene full-text and range indexes, the optimizer does not query the actual indexed nodes.

[adamretter]

I think this is one for @wolfgangmm as he has been looking at the Lucene index and regressions recently.

[adamretter]

• Copying from Ron van den Branden's post to the mailing list http://markmail.org/message/kmz6ecktup3ztvjr

I've just checked with the latest commit in the develop branch (d9ecd338b18eb1e6298881b5c2768c06b690a1ac), and the problem is still there.

Apart from my personal interest in this issue (I hope I'm not being too impatient) I wonder if this isn't an important issue in the light of a new release, since indirection seems quite a common coding pattern. For me, at least, this would be a major show-stopper for upgrading to eXist-3.0. Again, if there is anything I can do more besides reporting and testing (and fixing the issue myself, which I can't, unfortunately), I'd gladly do so. I hope the issue I reported at https://github.com/eXist-db/exist/issues/873 is clear and unambiguous; otherwise I would love to investigate how it can be improved.

[wolfgangmm]

My bugfix #949 addresses the failing range index queries. It does not improve the performance of indirect queries. This would be possible by making the optimizer inline the context variable (we do that for constants already). However, inlining is not always desirable. In some contexts inlining would have the contrary effect, in particular if there's no index defined. So the optimizer would need to take this into account. I'll have to think about this some more ...

[rvdb]

Ok, some progress, great! Is it wise, though, to close the issue? This way, you might risk sub-optimal performance for a common type of queries in eXist-3.0. Or should I isolate the performance of indirect queries in a separate issue?

[wolfgangmm]

Yes, right, the issue should not have been closed. Reopening it.

[Torcsi]

This issue is still there - it causes severe performance drop and blocks our upgrade to eXist 3.x.

A complete study is posted here Rewriting our xqls is nearly impossible; the pinpointed case is just one from the many...

[rvdb]

I've checked again for a (simple) real-life query with eXist-3.6.0: indeed, a "direct" query expression against the Lucene full-text index takes under 0.5 seconds to complete, while reformulation as an "indirect" query expression (where the context node is first declared as a separate variable) explodes the query execution time to over 5 seconds. That's a factor 10, still consistent with my first observation, it seems.

[rvdb]

Ahh, fortunately I've found a workaround (might be of interest to @Torcsi, since it requires minimal XQuery rewriting): if the "indirect" variable is first intersected (or united) with itself (I hope the example will clarify), optimization seems to work again!

Instead of the slow:

let $a := //SPEECH
return $a[ft:query(., 'hamlet')]

...which takes 2.74 seconds on the demo server to return 419 elements, this version does the job lightning fast:

let $a := //SPEECH
return ($a intersect $a)[ft:query(., 'hamlet')]

419 elements in 0.01 second, as you'd expect from eXist! The same goes for:

• ($a intersect $a)[ft:query(., 'hamlet')]
• ($a union $a)[ft:query(., 'hamlet')]
• ($a|$a)[ft:query(., 'hamlet')]

but not ($a,$a)[ft:query(., 'hamlet')], which is slow again. The Optimizer works in mysterious ways...

[joewiz]

@rvdb That's crazy! Thanks for the tip. Mysterious indeed.

[wolfgangmm]

That's a weird tip! I really need to have a closer look at what the optimizer does here.

[Torcsi]

@rvdb Thank you, we are working to proof this in our working environment.

It should be carefully tested if the two above would not degrade.

Isn't this all related to some lazy evaluation of the query?

Optimizers do magic, so those writing optimizers are therefore magicians...

[Torcsi]

Tested and found "where" to be also removed/reorganized as described in the query optimization docs.

Speed is like in 2.2, so this issue does not blocking us. Other issues though still obstruct switch to 3.x

[Torcsi]

We have run into further issues and decided to make a complete test. We have used 4.3 and our measures show 3.3-16x performance drop on synthetic exampples, totally obstructing porting to 4.3. All former tips were tested. The test environment and results are detailed here

Please advice what should we do. Is this the performance drop normal?