Enable cache-aware costs for index scan query plans

To improve query performance and reduce database I/O costs, you can enable cache-aware index scan costing in AlloyDB for PostgreSQL. When you enable cache-aware index scan costing, the AlloyDB query planner adjusts the estimated I/O cost of index scans based on how many index and table pages are available in the shared buffer when execution starts, and selects the final query plan based on those adjusted costs.

After you enable cache awareness, the feature functions automatically and adjusts when shared buffer hit rates change. Cache awareness can also work with other query tuning practices, such as setting the AlloyDB query planner cost estimate in random_page_cost.

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.