Обсуждение: Improve cache hit rate for OprCacheHash

Поиск
Список
Период
Сортировка

Improve cache hit rate for OprCacheHash

От
myzhen
Дата:
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

Re: Improve cache hit rate for OprCacheHash

От
Heikki Linnakangas
Дата:
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

Re:Re: Improve cache hit rate for OprCacheHash

От
myzhen
Дата:
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:
>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