Query Result Caching
When a query is executed, the result is persisted (that is, cached) for a period. At the end of the period, the result is purged from the system.
If a user repeats a query that has already been run, and the data in the tables has not changed since the last time the query was run, then the result of the query is the same. Instead of running the query again, Ingres returns the same result that it returned previously. This can substantially reduce query time because Ingres bypasses query execution and instead retrieves the result directly from the cache.
Typically, query results are reused if all of the following conditions are met:
• The new query syntactically matches the previously executed query.
• The table or view data contributing to the query result has not changed.
• The persisted result for the previous query is still available.
• The role accessing the cached results has the required privileges.
• Any configuration options that affect how the result was produced have not changed.
• The query does not use a function that must be evaluated each time it is run.
• The query does not reference external tables.
• The query does not reference catalogs.
• The query does not reference gateway tables.
• The query does not select blob columns.
• The query does not reference global temporary table.
• The query is not a scrollable cursor.
• The query is not an update cursor.
Query result caching will improve performance only when there are multiple requests of the same select query; otherwise, the cost overhead incurred when using this feature may negate its benefits.
Note: If the query references standard Ingres tables (btree,hash,heap,isam) and the isolation level is repeatable read or serializable, query result cache is not read. The query is rerun to acquire locks needed to enforce isolation.
Last modified date: 11/28/2023