Re: CTE, lateral or jsonb_object_agg ?

[Merlin Moncure]

On Sat, May 20, 2023 at 9:43 AM Marcos Pegoraro  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

>

Re: CTE, lateral or jsonb_object_agg ?

[Dominique Devienne]

On Sat, May 20, 2023 at 4:43 PM Marcos Pegoraro  wrote:

> I have a table like pg_settings, so records have name and value.
>

Hi. Maybe I'm missing something, but why aren't you simply doing:

select name, varvalue from sys_var where name = any($1)

and binding your 4 (in your examples) or 10 or 1 name(s) as a text array
(i.e. text[])?
With a prepared statement you plan only once.

You can also avoid selecting names by unnest'ing the text-array with
ordinality
and join+sort on it, to get the values in name order (i.e. same order as $1
you control).
See my recent thread where I learned about that technique.

Generating SQL text dynamically when you can avoid it with proper binding
and appropriate SQL is leaving performance on the table IMHO. --DD

>

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