Snowflake Inc.

Amazon Technologies, Inc.

Sangyong Hwang

Li Yan

Yongsik Yoon

Sourav Sikdar

Thierry Cruanes

Jiaqi Yan

Allison Waingold Lee

William H Waddington

Saileshwar Krishnamurthy

Kamal Kant Gupta

Nicola Dan Onose

Boyung Lee

Nitish Jindal

Xuelai Cui

Zixuan Geng

Adem Khachnaoui

Ippokratis Pandis

Michail Petropoulos

Justin Jon Levandoski

Murali Brahmadesam

Punit Rajgaria

Changan Han

Kartikey Bhatt

Aakash Shah

Nanda Kaushik

Walter Zhen Cai

Wesley A Leung

The subject technology receives a first query plan corresponding to a query, the first query plan comprising a set of join nodes, and a join order hint of a target query plan, each join node of the target query plan associated with a hint. The subject technology generates a second query plan to correct a set of vertical positions of a set of terminal nodes of the first query plan following the join order hint of the target query plan. The subject technology generates a third query plan to correct a set of lateral positions of the set of terminal nodes of the second query plan following the join order hint of the target query plan. The subject technology, for each join node from the set of join nodes of the third query plan, indicates that each join node has been hinted. The subject technology generates, after each join node of the third query plan has been indicated as being hinted, the target query plan based at least in part on the third query plan.

Rule based hint application

A query engine can use partition-granular level statistics to optimize query performance. A query can reference a table with a plurality of partitions and include a predicate. A partition-granular selectivity estimate for the predicate can be generated based on statistics stored regarding the plurality of partitions of the table. A query plan can be generated based on partition-granular selectivity estimate to optimize query processing.

Partition granular selectivity estimation for predicates

The subject technology receives a first query plan, the first query plan including a set of statements, the set of statements including at least one statement with at least a reference to a particular column in a first table. The subject technology identifies a first predicate from the at least one statement, the first predicate corresponding to a join operation. The subject technology determines, for the particular column, a set of expression properties. The subject technology, based on the set of expression properties, determines a first derived predicate, the first derived predicate corresponding to a first filter operation. The subject technology generates a new operation based on the first derived predicate. The subject technology generates a new query plan based at least in part on the join operation of first predicate, the first derived predicate, and the new operation of the first derived predicate.

Secure predicate derivation of queries using metadata

The subject technology receives a query. The subject technology generates a set of query blocks based on parsing the query. The subject technology stores query block metadata for each query block from the set of query blocks. The subject technology restores a set of logical query block boundaries. The subject technology performs a hash-based query block matching. The subject technology generates, after performing the hash-based query block matching, a final query plan. In an example, if a (same) query is executed again the same logical query blocks would produce the same metadata (e.g., identifier, name and hash). This metadata can be used to match logical query blocks between multiple executions of the same query.

Query block boundary identification

Techniques for join elimination are described herein. Join elimination can identify and eliminate unnecessary joins in a query plan node. For example, join elimination can involve a semantic query optimization technique, which removes reference to a table whose columns are only referenced in join predicates if the joins do not filter/expand rows in the result. Such joins can cause significant performance issues on larger datasets if not optimized.

Join elimination

The subject technology receives a query, the query including a set of statements for performing the query. The subject technology populates a compilation context based at least in part the query. The subject technology invokes a compiler to perform a compilation process based on the compilation context. The subject technology performs a lookup operation on a stored plan cache for an exact match based on information from the compilation context. The subject technology, in response to determining an exact match, determines whether the particular query plan requires re-compilation based on a data dependent optimization. The subject technology determines whether a plan cache entry corresponding to the particular query plan includes a data property constraint. The subject technology determines whether the data property constraint still holds based on a set of data properties.

Query plan cache in database systems

The subject technology provides embodiments for enabling derivation of predicates not only from other predicates but also from metadata such as expression properties. In examples, predicates are derived, avoiding unwanted impact on cardinality estimation. In other examples, predicates are derived, avoiding artificial runtime errors and providing a way to avoid security issues with secure views.

Change data capture is performed asynchronously for direct transfer to a stream processor. When updates to a database as part of database transactions are performed, a transaction log is generated that describes the database transactions. The transaction log may be evaluated to determine indications of changes included in the database transactions. The indications of the changes may be stored in memory according to the database transactions that include the indications of the changes. When a database transaction is determined to be committed to the database, the change indications may be sent directly from the memory to the stream processor.

Asynchronous change data capture for direct external transmission

Queries to a consistent view of a data set across different types of query engines are performed. Portions of a query received at a first type of query engine that can be performed at a different type of a query engine can be identified. A consistent view of a data set stored in a common data store can be provided by the first type of query engine to the second query engine. When the different type of query engine performs the portion of the query, the different type of query engine applies the consistent view of the data set. A result for the query can be returned by the first type of query engine based on results from the different type of query engine.

Performing queries to a consistent view of a data set across query engine types

The subject technology tracks a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query. The subject technology selects a first query plan among the plurality of query plans. The subject technology determines a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan. The subject technology attempts to execute a first query using the first query plan. The subject technology determines that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan. The subject technology executes the first query corresponding to the first query plan at a subsequent time using the second query plan.

Estimated execution time for query execution

Caching query plans in database systems

The subject technology receives a first query plan corresponding to a first query, the first query plan comprising a new query plan different than a previous query plan for the first query. The subject technology determines a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of the previous query plan. The subject technology, in response to determining that the value is greater than a threshold value, attempting to execute the first query using the first query plan, the attempting comprising determining that a second query plan selected among a plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan, and executing the first query at a subsequent time using the second query plan.

Dynamic query execution using estimated execution time

The subject technology receives a query, the query including a set of statements for performing the query. The subject technology populates a compilation context based at least in part the query. The subject technology provides the compilation context to a compiler. The subject technology invokes the compiler to perform a compilation process based on the compilation context, the compilation process comprising performing a lookup operation on a stored plan cache for an exact match based on information from the compilation context, the stored plan cache including a set of stored query plans, and determining whether the exact match of a particular query plan is found in the stored plan cache to avoid compiling the query using the compilation context.

Query plan caching for networked database systems

Alternative portions of a query plan to perform a query may be selected for processing partial results of a query. A query engine may generate a plan to perform a query that includes an operation that selects between different portions of the plan according to input data. The input data may be a partial result of the query generated by the data store instead of the query engine and provided to the query engine. The input data may be an individual item of the data set that is modified to a different version within a consistent view of the data set for the query.

Growing community of inventors

Sammamish, WA, United States of America

Sangyong Hwang