Optimize the Filter Reorder when the child operator is AND or OR

[wirybeaver]

When we ran the explain plan for the following query, we found out that the operator Filter_Full_Scan is executed before FILTER_INVERTED_INDEX, which causes the failure on applying docIdSet (the output of OR) on ScanBasedDocIdIterator ahead of time and thus the query is slowed down.

SQL

explain plan for
SELECT id,metadatas FROM rta_m3poc9proportioned WHERE env = 'production' AND (tags_prefixes = 'foo' OR tags_entries = 'bar') AND REGEXP_LIKE(tags_entries, 'bar(a.*\|b[^\.]*)') LIMIT 5000

Explain Result Operator	Operator_Id	Parent_Id
FILTER_INVERTED_INDEX(indexLookUp:inverted_index,operator:EQ,predicate:env = 'production')	11	6
FILTER_FULL_SCAN(operator:REGEXP_LIKE,predicate:regexp_like(tags_entries,'sourceZZZ('bar(a.|b[^.])')	10	6
FILTER_INVERTED_INDEX(indexLookUp:inverted_index,operator:EQ,predicate:tags_entries = 'bar')	9	7
FILTER_INVERTED_INDEX(indexLookUp:inverted_index,operator:EQ,predicate:tags_prefixes = 'foo')	8	7
FILTER_OR	7	6
FILTER_AND	6	5
DOC_ID_SET	5	4

It turns out that the existing reorderAndFilterChildOperators brutally assign a static priority value to the AND, OR operator. We should have recursively visit the child operators and use the lowest priority (largest value) of children as the priority of the current operator. In order to avoid walking through the tree twice, the reorder and priority evaluation should get done in the same recursive function. Reorder AND's children only. The PR is on the way.

[Jackie-Jiang]

You might also want to take a look at this related issue: #9839