Re: CTE, lateral or jsonb_object_agg ?

On Sat, May 20, 2023 at 9:43 AM Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> 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

>