Re: CTE, lateral or jsonb_object_agg ?

[Merlin Moncure <mmoncure@xxxxxxxxx>]

On Sat, May 20, 2023 at 9:43 AM Marcos Pegoraro <marcos@xxxxxxxxxx> wrote:

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;

Try the classic method:

select 

 max(varvalue) filter (where name = $$/Geral/BancoPadrao$$) as BancoPadrao,

 max(varvalue) filter (where name = $$/Geral/BancoMatricula$$ ) as  BancoMatricula,

 max(varvalue) filter (where name = $$/Geral/BancoParcela$$  ) as BancoParcela,

 max(varvalue) filter (where name = $$/Geral/BancoMaterial$$  ) as BancoMaterial

from sys_Var where Name = any('{/Geral/BancoPadrao,/Geral/BancoMatricula,/Geral/BancoParcela,/Geral/BancoMaterial}'::text[]);

Regardless, these timings are mostly below what I would consider to be the noise threshold; the actual query runtime is not much compared to the work the server has to spend setting up the query.  If you want real benefits for this kind of case, consider things like prepared statements (PREPARE/EXECUTE) and/or application caching.  You can also consider organizing "sys_var" into a record instead of a key value store.

merlin