Partition pruning by function

A computer-implemented method, comprising:

• executing, at a distributed database, a statement including a definition of a database table,
• wherein the executing of the statement creates, based at least on the definition, the database table to include at least one column storing a time datatype,
• wherein the executing of the statement further includes partitioning, based at least on the definition, the database table into a plurality of partitions in accordance with a plurality of values occupying the at least one column storing the time datatype,
• wherein the plurality of partitions includes a first partition including at least a first row from the database table in which a first value in the at least one column is within a first time range, a second partition including at least a second row from the database table in which a second value in the at least one column is within a second time range following the first time range, and a third partition including at least 1) a third row from the database table in which a third value in the at least one column is within a third time range preceding the first time range and 2) a fourth row from the database table in which a fourth value in the at least one column is within a fourth time range following the second time range, such that the third partition includes rows from the database table in which values in the at least one column are within neither the first time range nor the second time range,
• wherein the first partition is stored at a first node of the distributed database, the second partition is stored at a second node of the distributed database, and the third partition is stored at a third node of the distributed database;
• evaluating a query on the database table to identify a time based function included in the query;
• in response to the query including the time based function, pruning, from the query, the first partition of the database table based at least on the first time range of the first partition not overlapping with a time of the time based function, and pruning, from the query, the third partition of the database table based at least on the third and fourth time ranges of the third partition not overlapping with the time of the time based function;
• generating an execution plan for the query, the execution plan including a sequence of operations that avoids accessing the first partition and the third partition both of which being pruned from the query, wherein the sequence of operations accesses the second node storing the second partition but avoids access to the first node storing the first partition and the third node storing the third partition;
• and
• executing the execution plan for the query by at least accessing the second partition stored at the second node but not the first partition stored at the first node and the third partition stored at the third node.

A non-transitory computer readable medium storing instructions, which when executed by at least one data processor, result in operations comprising:

• executing, at a distributed database, a statement including a definition of a database table,
• wherein the executing of the statement creates, based at least on the definition, the database table to include at least one column storing a time datatype,
• wherein the executing of the statement further includes partitioning, based at least on the definition, the database table into a plurality of partitions in accordance with a plurality of values occupying the at least one column storing the time datatype,
• wherein the plurality of partitions includes a first partition including at least a first row from the database table in which a first value in the at least one column is within a first time range, a second partition including at least a second row from the database table in which a second value in the at least one column is within a second time range following the first time range, and a third partition including at least 1) a third row from the database table in which a third value in the at least one column is within a third time range preceding the first time range and 2) a fourth row from the database table in which a fourth value in the at least one column is within a fourth time range following the second time range, such that the third partition includes rows from the database table in which values in the at least one column are within neither the first time range nor the second time range,
• wherein the first partition is stored at a first node of the distributed database, the second partition is stored at a second node of the distributed database, and the third partition is stored at a third node of the distributed database;
• evaluating a query on the database table to identify a time based function included in the query;
• in response to the query including the time based function, pruning, from the query, the first partition of the database table based at least on the first time range of the first partition not overlapping with a time of the time based function, and pruning, from the query, the third partition of the database table based at least on the third and fourth time ranges of the third partition not overlapping with the time of the time based function;
• generating an execution plan for the query, the execution plan including a sequence of operations that avoids accessing the first partition and the third partition both of which being pruned from the query, wherein the sequence of operations accesses the second node storing the second partition but avoids access to the first node storing the first partition and the third node storing the third partition;
• and
• executing the execution plan for the query by at least accessing the second partition stored at the second node but not the first partition stored at the first node and the third partition stored at the third node.