Improve cache hit rate for OprCacheHash
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
On 22/08/2025 14:15, myzhen wrote:
The order of all schemas in OprCacheKey.search_path should be
meaningless.
No, operators live in schemas, just like tables and functions. To
demonstrate:
create schema schema1;
create schema schema2;
create function schema1.plus(int, int) RETURNS int AS $$ SELECT $1 + $1
$$ LANGUAGE SQL;
create function schema2.minus(int, int) RETURNS int AS $$ SELECT $1 - $1
$$ LANGUAGE SQL;
CREATE OPERATOR schema1.@+-@ (LEFTARG = int, RIGHTARG = int, FUNCTION=plus);
CREATE OPERATOR schema2.@+-@ (LEFTARG = int, RIGHTARG = int,
FUNCTION=minus);
postgres=# set search_path=schema1,schema2;
SET
postgres=# select 1 @+-@ 1;
?column?
----------
2
(1 row)
postgres=# set search_path=schema2,schema1;
SET
postgres=# select 1 @+-@ 1;
?column?
----------
0
(1 row)
- Heikki
Thanks for your reply,If multiple exact matches are found, it selects the first schema in activeSearchPath as the result. Therefore, the order of search_path is indeed meaningful.
At 2025-08-22 20:18:06, "Heikki Linnakangas" <hlinnaka@iki.fi> wrote:
Show quoted text
On 22/08/2025 14:15, myzhen wrote:
The order of all schemas in OprCacheKey.search_path should be
meaningless.No, operators live in schemas, just like tables and functions. To
demonstrate:create schema schema1;
create schema schema2;create function schema1.plus(int, int) RETURNS int AS $$ SELECT $1 + $1
$$ LANGUAGE SQL;
create function schema2.minus(int, int) RETURNS int AS $$ SELECT $1 - $1
$$ LANGUAGE SQL;CREATE OPERATOR schema1.@+-@ (LEFTARG = int, RIGHTARG = int, FUNCTION=plus);
CREATE OPERATOR schema2.@+-@ (LEFTARG = int, RIGHTARG = int,
FUNCTION=minus);postgres=# set search_path=schema1,schema2;
SET
postgres=# select 1 @+-@ 1;
?column?
----------
2
(1 row)postgres=# set search_path=schema2,schema1;
SET
postgres=# select 1 @+-@ 1;
?column?
----------
0
(1 row)- Heikki