CTE, lateral or jsonb_object_agg ?

[Marcos Pegoraro]

I have a table like pg_settings, so records have name and value.
This select is really fast, just 0.1 or 0.2 ms, but it runs millions of
times a day, so ...

Then all the time I have to select up to 10 of these records but the result
has to be a single record. So it can be done with ...

--Using CTE
with
BancoPadrao as (select varvalue from sys_var where name =
$$/Geral/BancoPadrao$$),
BancoMatricula as (select varvalue from sys_var where name =
$$/Geral/BancoMatricula$$),
BancoParcela as (select varvalue from sys_var where name =
$$/Geral/BancoParcela$$),
BancoMaterial as (select varvalue from sys_var where name =
$$/Geral/BancoMaterial$$)
select (select * from BancoPadrao) BancoPadrao,
       (select * from BancoMatricula) BancoMatricula,
       (select * from BancoParcela) BancoParcela,
       (select * from BancoMaterial) BancoMaterial;

--Using LATERAL
select * from (select varvalue from sys_var where name =
$$/Geral/BancoPadrao$$) BP(BancoPadrao)
cross join lateral (select varvalue from sys_var where name =
$$/Geral/BancoMatricula$$) BM(BancoMatricula)
cross join lateral (select varvalue from sys_var where name =
$$/Geral/BancoParcela$$) BPP(BancoParcela)
cross join lateral (select varvalue from sys_var where name =
$$/Geral/BancoMaterial$$) BMM(BancoMaterial);

--Using JSONB_OBJECT_AGG
select (VarValue->>'BancoPadrao') BancoPadrao,
       (VarValue->>'BancoMatricula') BancoMatricula,
       (VarValue->>'BancoParcela') BancoParcela,
       (VarValue->>'BancoMaterial') BancoMaterial
from (select jsonb_object_agg(split_part(name,'/',3), varvalue) VarValue
from sys_Var where Name
=  
any('{/Geral/BancoPadrao,/Geral/BancoMatricula,/Geral/BancoParcela,/Geral/BancoMaterial}'::text[]))
x

The first 2 options will have to find records independently, so they'll hit
heap or index multiple times. Then the first 2 options will have a bigger
planning time than the last one. But the last one has to aggregate and
later extract values from that aggregate.

Planning time for the first 2 options is 2 or 3 times more than the last
one but execution time is similar for all them.

Planning Time: 0.138 ms, Execution Time: 0.058 ms - First
Planning Time: 0.165 ms, Execution Time: 0.034 ms - Second
Planning Time: 0.073 ms, Execution Time: 0.040 ms - Third

My question is, how can I measure how much memory was used ? Because the
first 2 options did not have to calculate anything, they just found that
value and fetched, the last one had to process it in memory, right ?

regards,
Marcos