Details
Description
Creating multiple array indexes can lead to certain query's returning inconsistent results and having inconsistent plans that change on execution. The inconsistent plans do continue to use the same index but the "WHERE" clause is not always included.
Steps to recreate :
- Create bucket called test
- Create the following index :
CREATE INDEX `test_index` ON `test`((all (array (`doc`.`Id`) for `doc` in (`test_array`.`test_doc`) end)),(`test_array`.`test_doc`),`field`) WHERE (`type` = "type")
- Run the following query and note its plan text:
SELECT doc.`field_1`, doc.`Id`, count(meta(data).id) as cnt FROM test data UNNEST data.`test_array`.`test_doc` doc WHERE (data.`type`="type") AND (data.`field`="field") AND (doc.`Id` >= "0") AND (doc.`Type` = "doc_type") GROUP BY doc.`field_1`, doc.`Id`
- Create the following index :
CREATE INDEX test_inconsistent_1 ON test (ALL ARRAY t.id FOR t in diff_test_array END, `field`) WHERE `type`="type"
- Run the previous query multiple times. The execution plan will vary and would produce different results.
Attached my recreations results below.
Attachments
For Gerrit Dashboard: MB-33757 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
107670,2 | MB-33757. ResetIndexGroups() when Group pushdown is not possible | master | query | Status: MERGED | +2 | +1 |
107678,2 | MB-33757. ResetIndexGroups() when Group pushdown is not possible | alice | query | Status: MERGED | +2 | +1 |
107679,1 | MB-33757. ResetIndexGroups() when Group pushdown is not possible | vulcan | query | Status: ABANDONED | +2 | 0 |