From: | Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | CTE, lateral or jsonb_object_agg ? |
Date: | 2023-05-20 14:43:16 |
Message-ID: | CAB-JLwaqwCcf7s72VH5qEjRWuNnkmdiam7FXWUkahpSLqjdg3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2023-05-20 17:26:29 | Re: Adding SHOW CREATE TABLE |
Previous Message | Pedro Gonçalves | 2023-05-20 14:09:11 | DBeaver postgres localhost access |