[java][query] test_query_inner_join performs very slow on cbl side

• CBL / SG Version: couchbase-lite-java 2.7.0-91
• Steps to Reproduce:
  1. form a query:

  "LIMIT":10,"FROM":[{"AS":"route"},{"AS":"airline","JOIN":"INNER","ON":["AND",["AND",["=",[".airline.icao"],[".route.destinationairport"]],["=",[".airline.country"],"United States"]],["=",[".route.stops"],0]]}],"WHAT":[[".route.airline"],[".route.sourceairport"],[".airline.country"]]

  2. execute the query using cbl library on java desktop or webservice with centos 6 or 7 platform}}

• Actual Result: query returned correct result, but took over 5 mins
• Expected Result: query returns with correct result within reasonable time
• Logs :
  CBL LOGS: attached, Please search the verbose log with keyword of 341114.475ms
• Github link for the code: https://github.com/couchbaselabs/mobile-testkit/blob/master/testsuites/CBLTester/CBL_Functional_tests/SuiteSetup_FunctionalTests/test_query.py#L672
• Jenkins job failure link: http://uberjenkins.sc.couchbase.com:8080/job/CBLITE_Java-CentOS-7-TestServer-Qry-tests/9/testReport/testsuites.CBLTester.CBL_Functional_tests.SuiteSetup_FunctionalTests/test_query/test_query_inner_join_airline_sourceairport_country_country_stops_United_States_0_icao_destinationairport_10_/
• Pytest Command
  pytest -s --liteserv-version=2.7.0-80 --liteserv-host=172.23.104.36 --liteserv-port=8080 --xattrs --enable-file-logging --use-local-testserver --sync-gateway-version=2.7.0-163 --mode=cc --server-version=6.5.0-4912 --liteserv-platform=java-centos --create-db-per-suite=cbl-test --enable-sample-bucket=travel-sample testsuites/CBLTester/CBL_Functional_tests/SuiteSetup_FunctionalTests

• What is the last build this test passed:
  http://uberjenkins.sc.couchbase.com:8080/job/CBLITE_Java-CentOS-7-TestServer-Qry-tests/5/testReport/

Summary -

Often Query Test job on jenkins fails either for Java Desktop or Java WebService. I found this test_query_inner_join takes much longer than other query. I understand travel-sample database is large, but it maybe worth for dev can take a look its performance.

/CouchbaseLite/QUERY:

{Query#191} Compiling JSON query: {"LIMIT":10,"FROM":[{"AS":"route"},{"AS":"airline","JOIN":"INNER","ON":["AND",["AND",["=",[".airline.icao"],[".route.destinationairport"]],["=",[".airline.country"],"United States"]],["=",[".route.stops"],0]]}],"WHAT":[[".route.airline"],[".route.sourceairport"],[".airline.country"]]}
I/CouchbaseLite/QUERY:{Query#191}

Compiled as SELECT fl_result(fl_value("route".body, 'airline')), fl_result(fl_value("route".body, 'sourceairport')), fl_result(fl_value("airline".body, 'country')) FROM kv_default AS "route" INNER JOIN kv_default AS "airline" ON ((fl_value("airline".body, 'icao') = fl_value("route".body, 'destinationairport') AND fl_value("airline".body, 'country') = 'United States') AND fl_value("route".body, 'stops') = 0) AND ("airline".flags & 1 = 0) WHERE ("route".flags & 1 = 0) LIMIT MAX(0, 10)
I/CouchbaseLite/QUERY:

{QueryEnum#192}==> litecore::SQLiteQueryEnumerator 0x7f4a40be8aa8 @0x7f4a40be8aa8
I/CouchbaseLite/QUERY:{QueryEnum#192}

Created on

{Query#191}

with 10 rows (206 bytes) in 341114.475ms