Improve cache hit rate for OprCacheHash

From: myzhen <zhenmingyang(at)yeah(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Improve cache hit rate for OprCacheHash
Date: 2025-08-22 11:15:56
Message-ID: 301aaca2.1861.198d17e0ed3.Coremail.zhenmingyang@yeah.net
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear Hackers,
The order of all schemas in OprCacheKey.search_path should be meaningless. If we sort the search_path when constructing OprCacheKey, we can improve the hit rate of the operator cache (OprCacheHash). Otherwise, when the number and content of schemas in the search_path remain unchanged but the order of the schemas is different, the cache cannot be hit and an extra entry is wasted.
A possible example:
set search_path to schema1, schema2;
select * from test where col = 123; -- insert cache item for the first time.
select * from test where col = 123; -- cache hit.
set search_path to schema2, schema1; -- schema order change.
select * from test where col = 123; -- cache search failed, add a new cache item.

Add at the end of the make_oper_cache_key function:
qsort(key->search_path, MAX_CACHED_PATH_LEN, sizeof(Oid), oid_cmp);

I'm not sure if my understanding is correct or if it's worth making the change.

thanks!

regards

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2025-08-22 11:16:48 Re: Conflict detection for update_deleted in logical replication
Previous Message Dilip Kumar 2025-08-22 11:02:34 Re: Potential problem in commit f777d773878 and 4f7f7b03758