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