Postgres Pro
Downloads
Home   >   mailing lists

Thread: CTE, lateral or jsonb_object_agg ?

CTE, lateral or jsonb_object_agg ?

From
Marcos Pegoraro
Date:
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

Re: CTE, lateral or jsonb_object_agg ?

From
Dominique Devienne
Date:
On Sat, May 20, 2023 at 4:43 PM Marcos Pegoraro <marcos@f10.com.br> 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

Re: CTE, lateral or jsonb_object_agg ?

From
Merlin Moncure
Date:


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