Enable cache-aware costs for index scan query plans

This page documents cache awareness during index scans. When enabled, the AlloyDB for PostgreSQL query planner adjusts the estimated I/O cost of index scans based on how many index and table pages are already available in the shared buffer when execution starts. The final query plan is then selected based on the adjusted plan costs. This improves query performance and reduces database costs.

Once enabled, the cache awareness feature functions automatically and adjusts to the changing status of hitting the shared buffer. Additionally, cache awareness can work together with other query tuning practices, such as setting AlloyDB query planner's cost estimate to random_page_cost.

Tip: We recommend that you enable cache awareness if you notice the query planner doesn't select better index scan based query plans.

Enable cache-awareness

To enable cache awareness for your AlloyDB instance, set the alloydb.enable_cache_aware_costing (Preview) flag to on. Also, you can set the flag at the session level to affect the query plans occurring under the same session. For information on how to set the flag, see Configure an instance's database flags.

Example scenario

The following code sample shows an index scan plan executed with a fully warmed shared buffer cache.

explain (analyze, verbose, buffers)
SELECT count(d) FROM t1 WHERE a = 10 AND b > 100 AND c > 100;

------------------ Aggregate  (cost=3908.93..3908.94 rows=1 width=8) (actual time=4.128..4.130 rows=1 loops=1)
   Output: count(d)
   Buffers: shared hit=926
   ->  Index Scan using idx1 on public.t1  (cost=0.43..3906.49 rows=975 width=2) (actual time=0.143..3.205 rows=919 loops=1)
         Output: a, b, c, d
         Index Cond: ((t1.a = 10) AND (t1.b > 100) AND (t1.c > 100))
         Buffers: shared hit=926
   Execution Time: 4.353 ms

During this execution, there were no I/O reads. Without cache awareness, the query planner includes I/O cost for the index scan query plan. This can result in the index scan query plan losing to a sequential scan query plan.

The following code snippet shows the adjusted query plan cost when cache awareness is enabled.

explain (verbose)
SELECT count(d) FROM t1 WHERE a = 10 AND b > 100 AND c > 100;

------------------ Aggregate  (cost=29.93..29.94 rows=1 width=8)
   Output: count(d)
   ->  Index Scan using idx1 on public.t1  (cost=0.43..27.49 rows=975 width=2)
         Output: a, b, c, d
         Index Cond: ((t1.a = 10) AND (t1.b > 100) AND (t1.c > 100))

The new cost, 27.49, for the same index scan query plan is much lower than the old cost, 3906.49.