Query Plan Cache

This page covers how TigerGraph handles query execution and optimization through query plan caching. When a query is executed, the system parses, transforms, and optimizes it before generating a query plan in JSON format to guide its execution. While these steps are essential for processing the query, they can introduce significant overhead, especially when the same query is executed multiple times. To mitigate this overhead, TigerGraph caches the generated query plan in memory, reducing the time required to interpret subsequent executions of the same query.

Query Normalization

In many cases, users may frequently change constants in their queries. These changes would invalidate the cached query plan, making it necessary to re-interpret the query. To avoid reinterpreting the query, TigerGraph employs a process known as query normalization.

Query normalization helps maintain the validity of the cached query plan even when constants in the query change. Here’s how it works:

  1. Extract Constants: TigerGraph identifies and extracts constants from the query. For example, values like specific numbers or strings in the WHERE clause are identified as constants.

  2. Bind Variables: The extracted constants are replaced with query parameters. This ensures that even if the constant values change, the query text itself remains unchanged.

  3. Cache the Normalized Query Plan: The normalized query plan is cached, and since the text of the query doesn’t change (only the constants are replaced with parameters), the plan remains valid even when the constants change.

Example of Query Normalization

INTERPRET QUERY () {
 L = SELECT s
   FROM Person:s
   WHERE s.id >= 100 AND s.lastName == "Wang"
   ;
 R = SELECT t
   FROM L:s -(LIKES>:e)- Comment:t
   WHERE e.creationDate >= "1982-02-06 00:00:00" AND t.id >= 1200;
 PRINT R.size() AS result_size;
}

In the example above, the query has constants like 100 and "Wang", which are likely to change over time. After normalization, the query would look like this:

INTERPRET QUERY (INT GSQL_p1, STRING GSQL_p2, STRING GSQL_p3, INT GSQL_p4) {
  L = SELECT s
    FROM Person:s
    WHERE s.id >= GSQL_p1 AND s.lastName == GSQL_p2;
  R = SELECT t
    FROM L:s -(LIKES>:e)- Comment:t
    WHERE e.creationDate >= GSQL_p3 AND t.id >= GSQL_p4;
  PRINT R.size() AS result_size;
}

Here, the constants 100, "Wang", "1982-02-06 00:00:00", and 1200 have been replaced with query parameters (GSQL_p1, GSQL_p2, GSQL_p3, GSQL_p4). This allows the query plan to remain valid even if the values of these constants change in future executions.

Performance considerations

The query plan cache improves only the query interpretation phase by reusing a previously generated query plan. Specifically, it reduces the time required to parse, transform, optimize, and generate the query plan before the query is sent for execution.

After the query plan is generated, query execution continues through other TigerGraph components. The overall execution time of an interpreted query therefore depends on additional factors beyond the query plan cache, including:

  • Request queuing in RESTPP or GPE.

  • Inter-node communication in a multi-node cluster.

  • The amount of data processed by the query during execution.

  • Post-processing of the query result before it is returned.

As a result, subsequent executions of the same interpreted query are not guaranteed to complete faster than the first execution, even when the query plan is reused.

To accurately evaluate the effect of the query plan cache, compare executions of the same interpreted query using the same parameter values. Although query normalization allows different parameter values to reuse the cached query plan, different parameter values may cause the query to process different amounts of data, resulting in different execution times.

To understand where query execution time is spent, enable Query Profiling to view detailed execution metrics.

Why query plan cache primarily benefits interpreted queries

Query plan cache is primarily designed to improve the performance of interpreted queries.

Installed queries are compiled into native code when you run the INSTALL QUERY command. The parsing, transformation, optimization, and query plan generation steps are performed once during installation. Subsequent executions use the compiled query directly, so these steps are not performed again. This makes installed queries well suited for stable, frequently executed workloads because they incur the compilation overhead only once.

In contrast, interpreted queries are executed without an installation step. Each execution parses, transforms, optimizes, and generates a query plan before the query is executed. The query plan cache avoids repeating these steps by reusing a previously generated query plan for subsequent executions of the same interpreted query.

As a result, the query plan cache significantly reduces the overhead of repeatedly executing interpreted queries while preserving the flexibility of running queries without installing them. This makes interpreted queries well suited for scenarios such as ad hoc analysis, dynamically generated GSQL, and workflows where installing every query variation is impractical.

The choice between installed and interpreted queries involves a tradeoff between performance and flexibility. Installed queries require a one-time installation and provide the best execution performance for repeated use. Interpreted queries eliminate the installation step and allow queries to be executed immediately. By reducing the repeated query interpretation overhead, the query plan cache narrows the performance gap between repeated interpreted query executions and installed queries.

Query plan cache invalidation

Cached query plans are invalidated in the following cases:

  • Schema changes clear the entire query plan cache.

  • Changes to a query definition invalidate the cached plan for that query. Changes that only substitute constant values do not invalidate the cached query plan.

  • When the query plan cache reaches its configured capacity, older cached plans are evicted to make room for new entries.

The query plan cache is stored in memory and is cleared when GSQL is restarted.

There are two key configuration parameters for customizing the query plan cache. The GSQL.QueryPlanCache.Enable parameter enables the query plan cache, with the default value set to true. This ensures that query plans are cached to improve performance. Additionally, the GSQL.QueryPlanCache.Capacity parameter defines the maximum number of queries that can be stored in the cache, with a default value of 10,000. This capacity can be adjusted within the range of 1 to 100,000, depending on your system’s requirements.