Techniques for optimizing SQL statements using user-defined indexes with auxiliary properties

A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause the one or more processors to perform steps comprising:

• a database server registering one or more functions included in a user-defined index that includes a primary property and one or more auxiliary properties;
• wherein the primary property and each of the one or more auxiliary properties are operational properties that provide for evaluation operations that use the user-defined index, and wherein:
• the primary property is implemented in the one or more functions to provide for an evaluation operation of a user-defined operator that is associated with the user-defined index;
• and
• the one or more auxiliary properties are implemented in the one or more functions to provide for respective one or more evaluation operations that are different than, and in addition to, the evaluation operation of the user-defined operator that is associated with the user-defined index;
• receiving a query that specifies one or more parameters;
• generating a plurality of execution plans for evaluating the query;
• estimating a plurality of total costs respectively associated with the plurality of execution plans, wherein estimating a particular total cost associated with a particular execution plan comprises:
• the database server invoking the one or more functions with one or more function arguments based on the one or more parameters;
• the database server receiving one or more index costs from the one or more functions, wherein the one or more index costs are associated with using the one or more auxiliary properties of the user-defined index to evaluate the query, and wherein the one or more index costs are different than the cost of using the user-defined index to evaluate the user-defined operator associated with the user-defined index;
• and
• determining the particular total cost based on the one or more index costs;
• and
• selecting and storing an optimal execution plan from the plurality of execution plans, wherein the optimal execution plan is associated with an optimal total cost of the plurality of total costs.