Join optimization

A computer-implemented method comprising:

• receiving, using one or more processors, a join query, wherein the join query includes a join operation on a first table and a second table and includes a plurality of conditions, the second table including a first index on a first portion of the second table and a second index on a second portion of the second table, the plurality of conditions including a first condition and a second condition, the first condition based on the first portion of the second table, and the second condition based on the second portion of the second table;
• generating, using the one or more processors, a query plan based on the join query, wherein the query plan uses two or more indexes in a plurality of indexes, the plurality of indexes including the first index and the second index, to perform two or more index scans prior to a set operation and applies a multi-index augmented nested loop join after the set operation, wherein the set operation is based on results of the two or more index scans, and the two or more index scans include a first index scan using the first index and a second index scan using the second index;
• executing, using the one or more processors, the query plan, wherein executing the query plan includes:
• performing the first index scan of the second table using the first index to obtain a first result set;
• performing the second index scan of the second table using the second index to obtain a second result set;
• determining a first intermediate result set by applying the set operation to the first result set and the second result set;
• determining a second intermediate result set by performing a scan of the first table;
• determining a final result set by applying a multi-index augmented nested loop join to the second intermediate result set and at least a portion of the first intermediate result set;
• and
• providing, using the one or more processors, the final result set in response to the join query.