Query regressions due to indexer changes, 90% decrease in throughput

We are observing performance regressions in the following tests:

- Avg. Query Throughput (queries/sec). AG10. Aggregation query. Plasma. not_bounded 

- Avg. Query Throughput (queries/sec). AG3. Aggregation query. Plasma. not_bounded 

- Avg. Query Throughput (queries/sec). AG7. Aggregation query. Plasma. not_bounded 

- Avg. Query Throughput (queries/sec). Q20. Count (10K matches). 100M docs. Plasma. request_plus 

The following will be from AG10 test but I can supply information for the other tests upon request.

Test

Avg. Query Throughput (queries/sec). AG10. Aggregation query. Plasma. not_bounded 

Index

CREATE INDEX ag10 ON `bucket-1`(customer.zip, DATE_PART_STR(sold_date, 'year'), DATE_PART_STR(sold_date, 'quarter'), sales_price);

Query

SELECT customer.zip AS zip, SUM(sales_price) AS total_sales FROM `bucket-1` WHERE customer.zip IN $1 AND DATE_PART_STR(sold_date, 'year') = $2 AND DATE_PART_STR(sold_date, 'quarter') = $3 GROUP BY customer.zip ORDER BY customer.zip;

Results

6.5.0-3249: 1922.0 q/s

6.5.0-3250: 197.0 q/s

Report

http://cbmonitor.sc.couchbase.com/reports/html/?snapshot=arke_650-3249_access_cd09&snapshot=arke_650-3250_access_3ab9

Logs

6.5.0-3249

https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.12.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.13.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.14.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.15.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.18.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.19.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1287/172.23.97.20.zip

6.5.0-3250

https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.12.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.13.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.14.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.15.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.18.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.19.zip
https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-1281/172.23.97.20.zip

Changelog

http://172.23.123.43:8000/getchangelog?product=couchbase-server&fromb=6.5.0-3249&tob=6.5.0-3250

N1QL PProf

6.5.0-3249

http://perf.jenkins.couchbase.com/job/iris/14504/artifact/172.23.100.55_n1ql_cpu_b4a37f.pprof

6.5.0-3250

http://perf.jenkins.couchbase.com/job/iris/14505/artifact/172.23.100.55_n1ql_cpu_95f849.pprof

Comment

Looks like this issue is coming from one of two commits:

https://github.com/couchbase/indexing/commit/32fa083c01acc1e2ab21460b92a69eb1504e04c1

https://github.com/couchbase/plasma/commit/e70c7a45b676bb96de26440ca0c80d9ac7771fed