get_key_by_name should be marked as stable

[Iced-Sun]

The definition of the function get_key_by_name is:

CREATE OR REPLACE FUNCTION pgsodium.get_key_by_name(text)
 RETURNS pgsodium.valid_key
 LANGUAGE sql
 STABLE SECURITY DEFINER
 SET search_path TO ''
AS $function$
    SELECT * from pgsodium.valid_key WHERE name = $1;
$function$
;

By default it is marked as volatile (ref) and thus prohibits the index scan when used as the comparison value (ref).

---

For example, the plan for query

select * from citizen
where id = pgsodium.crypto_shorthash('plain', (pgsodium.get_key_by_name('default-siphash-key')).id);

is

Seq Scan on citizen  (cost=0.00..7630.92 rows=1 width=112)
  Filter: (id = pgsodium.crypto_shorthash('\x706c61696e'::bytea, (pgsodium.get_key_by_name('default-siphash-key'::text)).id))

---

The plan for an alternative query

select * from citizen
where id = pgsodium.crypto_shorthash('plain', (select id from pgsodium.valid_key where name = 'default-siphash-key'))

is

Index Scan using citizen_pkey on citizen  (cost=2.91..5.12 rows=1 width=112)
  Index Cond: (id = pgsodium.crypto_shorthash('\x706c61696e'::bytea, $0))
  InitPlan 1 (returns $0)
    ->  Index Scan using pgsodium_key_unique_name on key  (cost=0.14..2.37 rows=1 width=16)
          Index Cond: (name = 'default-siphash-key'::text)
          Filter: ((status = ANY ('{valid,default}'::pgsodium.key_status[])) AND CASE WHEN (expires IS NULL) THEN true ELSE (expires > now()) END)

---

By marking get_key_by_name as stable (alter function pgsodium.get_key_by_name stable), the plan for the first query is now

Index Scan using citizen_pkey on citizen  (cost=0.79..3.00 rows=1 width=112)
  Index Cond: (id = pgsodium.crypto_shorthash('\x706c61696e'::bytea, (pgsodium.get_key_by_name('default-siphash-key'::text)).id))

Generally, the performance improvement is usually significant for index-scan vs. seq-scan. Please consider to mark get_key_by_name and get_key_by_id as stable.

Regards.