Thread

1. Improve cache hit rate for OprCacheHash

   myzhen <zhenmingyang@yeah.net> — 2025-08-22T11:15:56Z

   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

2. Re: Improve cache hit rate for OprCacheHash

   Heikki Linnakangas <hlinnaka@iki.fi> — 2025-08-22T12:18:06Z

   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
   
   
   
   
   

3. Re:Re: Improve cache hit rate for OprCacheHash

   myzhen <zhenmingyang@yeah.net> — 2025-08-22T14:08:52Z

   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