Cardinality and selectivity estimation using a single table join index

A method of estimating cardinality of an aggregation, the method comprising:

• receiving, with a processor, a database query, the query comprising one or more query predicates and referencing one or more database tables, the database query including one or more GROUP-BY fields;
• identifying, with the processor, one or more join indexes, wherein each of the one or more join index(es) is respectively defined on a single one of database tables referenced by the database query;
• calculating, with the processor, a row count of rows selected by the query predicates at least partly using a row count of the one or more join indexes or statistics on the one or more join indexes;
• and
• calculating, with the processor, the cardinality of the aggregation at least partly from the calculated row count.

A non-transitory computer-readable medium having stored thereon computer-executable instructions that when executed on a computing device cause the computing device to perform a method of estimating selectivity of a base table predicate, the method comprising:

• receiving a database query, the query comprising one or more query predicates and referencing one or more database tables;
• identifying one or more join indexes, wherein each of the one or more join index(es) is respectively defined on a single one of database tables referenced by the database query, the join index(es) comprising one or more join index predicates;
• calculating a row count of rows selected by the query predicates at least partly using a row count of the one or more join indexes or statistics of the one or more join indexes;
• and
• calculating the selectivity of the base table predicate at least partly from the calculated row count.

A non-transitory computer-readable medium having stored thereon computer-executable instructions that when executed on a computing device cause the computing device to perform a method of estimating cardinality of an aggregation, the method comprising:

• receiving a database query, the query comprising one or more query predicates and referencing one or more database tables, the database query including one or more GROUP-BY fields;
• identifying one or more join indexes, wherein each of the one or more join index(es) is respectively defined on a single one of database tables referenced by the database query;
• calculating a row count of rows selected by the query predicates at least partly using a row count of the one or more join indexes or statistics on the one or more join indexes;
• and
• calculating the cardinality of the aggregation at least partly from the calculated row count.