US 12,717,792 B2Granted

Push down top-k information for group-by grouping sets and window function operator

AssigneeSnowflake Inc.

InventorsBowei Chen, Corbin McElhanney, Eric Alan Musser, Gopal Vijayaraghavan

Life of this patent

Estimating the term…

Loading patent…

Abstract

Various example embodiments described herein provide for systems, methods, devices, instructions, and the like for optimizing a query plan for executing a query by pushing down top-k information in the query plan with respect to a group-by grouping sets operator or a window function operator, which can be used within a data platform (e.g., database) environment. In particular, various example embodiments enable information from a top-k operator of a query plan to be pushed down the query plan and enable pushing the information to a select operator (e.g., aggregation operator) positioned below the top-k operator that implements the group-by grouping sets operator or the window function operator.

Claims20

Dependent claims

A system comprising:

at least one processor;
and
at least one memory storing instructions that cause the at least one processor to perform operations comprising:
accessing a query plan for execution on a set of tables, the query plan comprising a plurality of operators, the plurality of operators comprising a top-k operator configured to order rows by a set of order-by keys and to limit a number of rows provided by the top-k operator to a specific number of rows;
determining whether the query plan includes a group-by grouping sets operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the group-by grouping sets operator positioned below the top-k operator:
analyzing the group-by grouping sets operator, the analyzing of the group-by grouping sets operator comprises determining whether the group-by grouping sets operator is configured to cause nulls to appear after non-nulls;
and
in response to determining that the group-by grouping sets operator is configured to cause nulls to appear after non-nulls, modifying the query plan by changing a first aggregation operator positioned below the group-by grouping sets operator in the query plan to apply a top-k operation based on information from the top-k operator;
determining whether the query plan includes a window function operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the window function operator positioned below the top-k operator:
analyzing the window function operator to determine whether the window function operator satisfies a set of conditions, the set of conditions comprising a condition that a set of partition keys of the window function operator is a prefix of the set of order-by keys;
and
in response to determining that the window function operator satisfies the set of conditions, modifying the query plan by changing a second aggregation operator positioned below the window function operator in the query plan to apply the top-k operation based on the information from the top-k operator;
and executing the query plan.

12 more elements

A method comprising:

accessing, by at least one processor, a query plan for execution a set of tables, the query plan comprising a plurality of operators, the plurality of operators comprising a top-k operator configured to order rows by a set of order-by keys and to limit a number of rows provided by the top-k operator to a specific number of rows;
determining, by the at least one processor, that the query plan includes a group-by grouping sets operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the group-by grouping sets operator positioned below the top-k operator:
analyzing, by the at least one processor, the group-by grouping sets operator, the analyzing of the group-by grouping sets operator comprises determining that the group-by grouping sets operator is configured to cause nulls to appear after non-nulls;
and
in response to determining that the group-by grouping sets operator is configured to cause nulls to appear after non-nulls, modifying, by the at least one processor, the query plan by changing a first aggregation operator positioned below the group-by grouping sets operator in the query plan to apply a top-k operation based on information from the top-k operator;
determining, by the at least one processor, that the query plan includes a window function operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the window function operator positioned below the top-k operator:
analyzing, by the at least one processor, the window function operator to determine that the window function operator satisfies a set of conditions, the set of conditions comprising a condition that a set of partition keys of the window function operator is a prefix of the set of order-by keys;
and
in response to determining that the window function operator satisfies the set of conditions, modifying, by the at least one processor, the query plan by changing a second aggregation operator positioned below the window function operator in the query plan to apply the top-k operation based on the information from the top-k operator;
and executing, by the at least one processor, the query plan.

9 more elements

A non-transitory machine-storage medium storing instructions that when executed by a machine, cause the machine to perform operations comprising:

accessing a query plan for execution on a set of tables, the query plan comprising a plurality of operators, the plurality of operators comprising a top-k operator configured to order rows by a set of order-by keys and to limit a number of rows provided by the top-k operator to a specific number of rows;
determining whether the query plan includes a group-by grouping sets operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the group-by grouping sets operator positioned below the top-k operator:
analyzing the group-by grouping sets operator, the analyzing of the group-by grouping sets operator comprises determining whether the group-by grouping sets operator is configured to cause nulls to appear after non-nulls;
and
in response to determining that the group-by grouping sets operator is configured to cause nulls to appear after non-nulls, modifying the query plan by changing a first aggregation operator positioned below the group-by grouping sets operator in the query plan to apply a top-k operation based on information from the top-k operator;
determining whether the query plan includes a window function operator positioned below the top-k operator in the query plan;
in response to determining that the query plan includes the window function operator positioned below the top-k operator:
analyzing the window function operator to determine whether the window function operator satisfies a set of conditions, the set of conditions comprising a condition that a set of partition keys of the window function operator is a prefix of the set of order-by keys;
and
in response to determining that the window function operator satisfies the set of conditions, modifying the query plan by changing a second aggregation operator positioned below the window function operator in the query plan to apply the top-k operation based on the information from the top-k operator;
and executing the query plan.

9 more elements

Dependencies are inferred from the claim text.