рд╣рд╛рдореА "рд╕рд╛рдзрд╛рд░рдг рджреЗрдЦрд┐рдиреЗ рд╕рд░рд▓" PostgreSQL рдкреНрд░рд╢реНрдирд╣рд░реВрдХреЛ рдкреНрд░рджрд░реНрд╢рди рд╕реБрдзрд╛рд░ рдЧрд░реНрди рдереЛрд░реИ рдЬреНрдЮрд╛рдд рддрд░рд┐рдХрд╛рд╣рд░реВрдХреЛ рдЕрдзреНрдпрдпрдирдорд╛ рд╕рдорд░реНрдкрд┐рдд рд▓реЗрдЦрд╣рд░реВрдХреЛ рд╢реНрд░реГрдВрдЦрд▓рд╛ рдЬрд╛рд░реА рд░рд╛рдЦреНрдЫреМрдВ:
рджреБрд░реНрд▓рдн рд░реЗрдХрд░реНрдб JOIN рдХреЛ рдмреАрдЪрдорд╛ рдкреБрдЧреНрдиреЗрдЫ рд╕рд┐рд╕рд┐рдлрд┐рдпрди рдЬреЛрдЗрди рдПрд░реЗ рд╣рд╛рдирд┐рдХрд╛рд░рдХ JOIN рд░ OR CTE CTE рдорд╛ рд╕рд╛рдореЗрд▓ рд╣реБрдиреБрд╣реЛрд╕реН
рдорд▓рд╛рдИ JOIN рддреНрдпрддрд┐ рдорди рдкрд░реНрджреИрди рднрдиреНрдиреЗ рдирд╕реЛрдЪ... :)
рддрд░ рдЕрдХреНрд╕рд░ рдпреЛ рдмрд┐рдирд╛, рдЕрдиреБрд░реЛрдз рдпрд╕рдХреЛ рд╕рд╛рде рднрдиреНрджрд╛ рдЙрд▓реНрд▓реЗрдЦрдиреАрдп рд░реВрдкрдорд╛ рдЕрдзрд┐рдХ рдЙрддреНрдкрд╛рджрдХ рд╣реБрди рдЬрд╛рдиреНрдЫред рддреНрдпрд╕реИрд▓реЗ рдЖрдЬ рд╣рд╛рдореА рдкреНрд░рдпрд╛рд╕ рдЧрд░реНрдиреЗрдЫреМрдВ рд╕рдВрд╕рд╛рдзрди-рдЧрд╣рди JOIN рдмрд╛рдЯ рдЫреБрдЯрдХрд╛рд░рд╛ рдкрд╛рдЙрдиреБрд╣реЛрд╕реН - рдПрдХ рд╢рдмреНрджрдХреЛрд╢ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрджреИред
PostgreSQL 12 рдмрд╛рдЯ рд╕реБрд░реБ рдЧрд░реНрджреИ, рддрд▓ рд╡рд░реНрдгрди рдЧрд░рд┐рдПрдХрд╛ рдХреЗрд╣реА рдЕрд╡рд╕реНрдерд╛рд╣рд░реВрд▓рд╛рдИ рдЕрд▓рд┐ рдлрд░рдХ рд░реВрдкрдорд╛ рдкреБрди: рдЙрддреНрдкрд╛рджрди рдЧрд░реНрди рд╕рдХрд┐рдиреНрдЫред
рдкреВрд░реНрд╡рдирд┐рд░реНрдзрд╛рд░рд┐рдд рдЧреИрд░-рднреМрддрд┐рдХреАрдХрд░рдг CTE ред рдпреЛ рд╡реНрдпрд╡рд╣рд╛рд░ рдХреБрдЮреНрдЬреА рдирд┐рд░реНрджрд┐рд╖реНрдЯ рдЧрд░реЗрд░ рдЙрд▓реНрдЯрд╛рдЙрди рд╕рдХрд┐рдиреНрдЫMATERIALIZED
.
рд╕реАрдорд┐рдд рд╢рдмреНрджрд╛рд╡рд▓реАрдорд╛ рдзреЗрд░реИ "рддрдереНрдпрд╣рд░реВ"
рдПрдХ рдзреЗрд░реИ рд╡рд╛рд╕реНрддрд╡рд┐рдХ рдЕрдиреБрдкреНрд░рдпреЛрдЧ рдХрд╛рд░реНрдп рд▓рд┐рдиреБрд╣реЛрд╕реН - рд╣рд╛рдореАрд▓реЗ рдПрдЙрдЯрд╛ рд╕реВрдЪреА рдкреНрд░рджрд░реНрд╢рди рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ
25.01 | ╨Ш╨▓╨░╨╜╨╛╨▓ ╨Ш.╨Ш. | ╨Я╨╛╨┤╨│╨╛╤В╨╛╨▓╨╕╤В╤М ╨╛╨┐╨╕╤Б╨░╨╜╨╕╨╡ ╨╜╨╛╨▓╨╛╨│╨╛ ╨░╨╗╨│╨╛╤А╨╕╤В╨╝╨░.
22.01 | ╨Ш╨▓╨░╨╜╨╛╨▓ ╨Ш.╨Ш. | ╨Э╨░╨┐╨╕╤Б╨░╤В╤М ╤Б╤В╨░╤В╤М╤О ╨╜╨░ ╨е╨░╨▒╤А: ╨╢╨╕╨╖╨╜╤М ╨▒╨╡╨╖ JOIN.
20.01 | ╨Я╨╡╤В╤А╨╛╨▓ ╨Я.╨Я. | ╨Я╨╛╨╝╨╛╤З╤М ╨╛╨┐╤В╨╕╨╝╨╕╨╖╨╕╤А╨╛╨▓╨░╤В╤М ╨╖╨░╨┐╤А╨╛╤Б.
18.01 | ╨Ш╨▓╨░╨╜╨╛╨▓ ╨Ш.╨Ш. | ╨Э╨░╨┐╨╕╤Б╨░╤В╤М ╤Б╤В╨░╤В╤М╤О ╨╜╨░ ╨е╨░╨▒╤А: JOIN ╤Б ╤Г╤З╨╡╤В╨╛╨╝ ╤А╨░╤Б╨┐╤А╨╡╨┤╨╡╨╗╨╡╨╜╨╕╤П ╨┤╨░╨╜╨╜╤Л╤Е.
16.01 | ╨Я╨╡╤В╤А╨╛╨▓ ╨Я.╨Я. | ╨Я╨╛╨╝╨╛╤З╤М ╨╛╨┐╤В╨╕╨╝╨╕╨╖╨╕╤А╨╛╨▓╨░╤В╤М ╨╖╨░╨┐╤А╨╛╤Б.
рд╕рд╛рд░ рд╕рдВрд╕рд╛рд░рдорд╛, рдХрд╛рд░реНрдп рд▓реЗрдЦрдХрд╣рд░реВ рд╣рд╛рдореНрд░реЛ рд╕рдВрдЧрдардирдХрд╛ рд╕рдмреИ рдХрд░реНрдордЪрд╛рд░реАрд╣рд░реВ рдмреАрдЪ рд╕рдорд╛рди рд░реВрдкрдорд╛ рд╡рд┐рддрд░рд┐рдд рд╣реБрдиреБрдкрд░реНрдЫ, рддрд░ рд╡рд╛рд╕реНрддрд╡рд┐рдХрддрд╛рдорд╛ рдХрд╛рд░реНрдпрд╣рд░реВ, рдПрдХ рдирд┐рдпрдордХреЛ рд░реВрдкрдорд╛, рдорд╛рдирд┐рд╕рд╣рд░реВрдХреЛ рдПрдХрджрдо рд╕реАрдорд┐рдд рд╕рдВрдЦреНрдпрд╛рдмрд╛рдЯ рдЖрдЙрдБрдЫрдиреН - "рд╡реНрдпрд╡рд╕реНрдерд╛рдкрдирдмрд╛рдЯ" рдкрджрд╛рдиреБрдХреНрд░рдо рдорд╛рдерд┐ рд╡рд╛ рдЫрд┐рдореЗрдХреА рд╡рд┐рднрд╛рдЧрд╣рд░реВ (рд╡рд┐рд╢реНрд▓реЗрд╖рдХ, рдбрд┐рдЬрд╛рдЗрдирд░, рдорд╛рд░реНрдХреЗрдЯрд┐рдЩ, ...) рдмрд╛рдЯ "рдЙрдкрдХрдиреНрдЯреНрд░реНрдпрд╛рдХреНрдЯрд░рд╣рд░реВрдмрд╛рдЯ"ред
рд╕реНрд╡реАрдХрд╛рд░ рдЧрд░реМрдВ рдХрд┐ 1000 рд╡реНрдпрдХреНрддрд┐рд╣рд░реВрдХреЛ рд╣рд╛рдореНрд░реЛ рд╕рдВрдЧрдардирдорд╛, рдХреЗрд╡рд▓ 20 рд▓реЗрдЦрдХрд╣рд░реВ (рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛ рдкрдирд┐ рдХрдо) рдкреНрд░рддреНрдпреЗрдХ рд╡рд┐рд╢рд┐рд╖реНрдЯ рдХрд▓рд╛рдХрд╛рд░рдХреЛ рд▓рд╛рдЧрд┐ рдХрд╛рд░реНрдпрд╣рд░реВ рд╕реЗрдЯ рдЧрд░реНрдЫрдиреН рд░
рд▓рд┐рдкрд┐ рдЬрдирд░реЗрдЯрд░
-- ╤Б╨╛╤В╤А╤Г╨┤╨╜╨╕╨║╨╕
CREATE TABLE person AS
SELECT
id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
generate_series(1, 1000) id;
ALTER TABLE person ADD PRIMARY KEY(id);
-- ╨╖╨░╨┤╨░╤З╨╕ ╤Б ╤Г╨║╨░╨╖╨░╨╜╨╜╤Л╨╝ ╤А╨░╤Б╨┐╤А╨╡╨┤╨╡╨╗╨╡╨╜╨╕╨╡╨╝
CREATE TABLE task AS
WITH aid AS (
SELECT
id
, array_agg((random() * 999)::integer + 1) aids
FROM
generate_series(1, 1000) id
, generate_series(1, 20)
GROUP BY
1
)
SELECT
*
FROM
(
SELECT
id
, '2020-01-01'::date - (random() * 1e3)::integer task_date
, (random() * 999)::integer + 1 owner_id
FROM
generate_series(1, 100000) id
) T
, LATERAL(
SELECT
aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
FROM
aid
WHERE
id = T.owner_id
LIMIT 1
) a;
ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);
рдПрдХ рд╡рд┐рд╢реЗрд╖ рдХрд╛рд░реНрдпрдХрд╛рд░реАрдХреЛ рд▓рд╛рдЧрд┐ рдЕрдиреНрддрд┐рдо 100 рдХрд╛рд░реНрдпрд╣рд░реВ рджреЗрдЦрд╛рдЙрдиреБрд╣реЛрд╕реН:
SELECT
task.*
, person.name
FROM
task
LEFT JOIN
person
ON person.id = task.author_id
WHERE
owner_id = 777
ORDER BY
task_date DESC
LIMIT 100;
рдпрд╕рд▓реЗ рддреНрдпрд╕рдмрд╛рдЯ рд╣рдЯрд╛рдЙрдБрдЫ 1/3 рдХреБрд▓ рд╕рдордп рд░ 3/4 рдкрдврд╛рдЗ рдкреНрд░рддреНрдпреЗрдХ рдЖрдЙрдЯрдкреБрдЯ рдХрд╛рд░реНрдпрдХреЛ рд▓рд╛рдЧрд┐ 100 рдкрдЯрдХ рд▓реЗрдЦрдХ рдЦреЛрдЬреНрдирдХреЛ рд▓рд╛рдЧрд┐ рдбрд╛рдЯрд╛рдХреЛ рдкреГрд╖реНрдард╣рд░реВ рдорд╛рддреНрд░ рдмрдирд╛рдЗрдпреЛред рддрд░ рд╣рд╛рдореАрд▓рд╛рдИ рдерд╛рд╣рд╛ рдЫ рдХрд┐ рдпреА рд╕рдпреМрдВ рдордзреНрдпреЗ рдХреЗрд╡рд▓ 20 рдлрд░рдХ - рдпреЛ рдЬреНрдЮрд╛рди рдкреНрд░рдпреЛрдЧ рдЧрд░реНрди рд╕рдореНрднрд╡ рдЫ?
hstore-рд╢рдмреНрджрдХреЛрд╢
рд╕рджреБрдкрдпреЛрдЧ рдЧрд░реМрдВ
CREATE EXTENSION hstore
рд╣рд╛рдореАрд▓реЗ рдХреЗрд╡рд▓ рд▓реЗрдЦрдХрдХреЛ рдЖрдИрдбреА рд░ рдЙрдирдХреЛ рдирд╛рдо рд╢рдмреНрджрдХреЛрд╢рдорд╛ рд░рд╛рдЦреНрдиреБ рдкрд░реНрдЫ рддрд╛рдХрд┐ рд╣рд╛рдореА рдпреЛ рдХреБрдЮреНрдЬреА рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рдирд┐рдХрд╛рд▓реНрди рд╕рдХреНрдЫреМрдВ:
-- ╤Д╨╛╤А╨╝╨╕╤А╤Г╨╡╨╝ ╤Ж╨╡╨╗╨╡╨▓╤Г╤О ╨▓╤Л╨▒╨╛╤А╨║╤Г
WITH T AS (
SELECT
*
FROM
task
WHERE
owner_id = 777
ORDER BY
task_date DESC
LIMIT 100
)
-- ╤Д╨╛╤А╨╝╨╕╤А╤Г╨╡╨╝ ╤Б╨╗╨╛╨▓╨░╤А╤М ╨┤╨╗╤П ╤Г╨╜╨╕╨║╨░╨╗╤М╨╜╤Л╤Е ╨╖╨╜╨░╤З╨╡╨╜╨╕╨╣
, dict AS (
SELECT
hstore( -- hstore(keys::text[], values::text[])
array_agg(id)::text[]
, array_agg(name)::text[]
)
FROM
person
WHERE
id = ANY(ARRAY(
SELECT DISTINCT
author_id
FROM
T
))
)
-- ╨┐╨╛╨╗╤Г╤З╨░╨╡╨╝ ╤Б╨▓╤П╨╖╨░╨╜╨╜╤Л╨╡ ╨╖╨╜╨░╤З╨╡╨╜╨╕╤П ╤Б╨╗╨╛╨▓╨░╤А╤П
SELECT
*
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
T;
рд╡реНрдпрдХреНрддрд┐рд╣рд░реВрдХреЛ рдмрд╛рд░реЗрдорд╛ рдЬрд╛рдирдХрд╛рд░реА рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрди рдЦрд░реНрдЪ реи рдЧреБрдгрд╛ рдХрдо рд╕рдордп рд░ рен рдЧреБрдгрд╛ рдХрдо рдбрд╛рдЯрд╛ рдкрдврд┐рдиреНрдЫ! "рд╢рдмреНрджрд╛рд╡рд▓реА" рдХреЛ рдЕрддрд┐рд░рд┐рдХреНрдд, рдХреБрди рдХреБрд░рд╛рд▓реЗ рд╣рд╛рдореАрд▓рд╛рдИ рдпреА рдкрд░рд┐рдгрд╛рдорд╣рд░реВ рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрди рдорджреНрджрдд рдЧрд░реНтАНрдпреЛ рдмрд▓реНрдХ рд░реЗрдХрд░реНрдб рдкреБрди: рдкреНрд░рд╛рдкреНрддрд┐ рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рдПрдХрд▓ рдкрд╛рд╕рдорд╛ рддрд╛рд▓рд┐рдХрд╛рдмрд╛рдЯ = ANY(ARRAY(...))
.
рддрд╛рд▓рд┐рдХрд╛ рдкреНрд░рд╡рд┐рд╖реНрдЯрд┐рд╣рд░реВ: рдХреНрд░рдорд┐рдХрдХрд░рдг рд░ рдбрд┐рд╕реЗрд░рд┐рдпрд▓рд╛рдЗрдЬреЗрд╢рди
рддрд░ рдХреЗ рд╣реБрдиреНрдЫ рдпрджрд┐ рд╣рд╛рдореАрд▓реЗ рдПрдЙрдЯрд╛ рдкрд╛рда рдХреНрд╖реЗрддреНрд░ рдорд╛рддреНрд░ рд╣реЛрдЗрди, рддрд░ рд╢рдмреНрджрдХреЛрд╢рдорд╛ рд╕рдореНрдкреВрд░реНрдг рдкреНрд░рд╡рд┐рд╖реНрдЯрд┐ рдмрдЪрдд рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ? рдпрд╕ рдЕрд╡рд╕реНрдерд╛рдорд╛, PostgreSQL рдХреЛ рдХреНрд╖рдорддрд╛рд▓реЗ рд╣рд╛рдореАрд▓рд╛рдИ рдорджреНрджрдд рдЧрд░реНрдиреЗрдЫ рддрд╛рд▓рд┐рдХрд╛ рдкреНрд░рд╡рд┐рд╖реНрдЯрд┐рд▓рд╛рдИ рдПрдХрд▓ рдорд╛рдирдХреЛ рд░реВрдкрдорд╛ рд╡реНрдпрд╡рд╣рд╛рд░ рдЧрд░реНрдиреБрд╣реЛрд╕реН:
...
, dict AS (
SELECT
hstore(
array_agg(id)::text[]
, array_agg(p)::text[] -- ╨╝╨░╨│╨╕╤П #1
)
FROM
person p
WHERE
...
)
SELECT
*
, (((TABLE dict) -> author_id::text)::person).* -- ╨╝╨░╨│╨╕╤П #2
FROM
T;
рдпрд╣рд╛рдБ рдХреЗ рднрдЗрд░рд╣реЗрдХреЛ рдерд┐рдпреЛ рд╣реЗрд░реМрдВ:
- рд╣рд╛рдореАрд▓реЗ рд▓рд┐рдпреМрдВ p рдкреВрд░реНрдг рд╡реНрдпрдХреНрддрд┐ рддрд╛рд▓рд┐рдХрд╛ рдкреНрд░рд╡рд┐рд╖реНрдЯрд┐рдХреЛ рд▓рд╛рдЧрд┐ рдЙрдкрдирд╛рдордХреЛ рд░реВрдкрдорд╛ рд░ рддрд┐рдиреАрд╣рд░реВрдХреЛ рдПрд░реНрд░реЗ рднреЗрд▓рд╛ рднрдпреЛред
- рдпреЛ рд░реЗрдХрд░реНрдбрд┐рдЩ рдХреЛ рдПрд░реЗ рдкреБрди: рдХрд╛рд╕реНрдЯ рдЧрд░рд┐рдпреЛ рдкрд╛рда рд╕реНрдЯреНрд░рд┐рдЩрдХреЛ рдПрд░реНрд░реЗрдорд╛ (рд╡реНрдпрдХреНрддрд┐[]::text[]) рдпрд╕рд▓рд╛рдИ hstore рд╢рдмреНрджрдХреЛрд╢рдорд╛ рдорд╛рдирд╣рд░реВрдХреЛ рдПрд░реЗрдХреЛ рд░реВрдкрдорд╛ рд░рд╛рдЦреНрдиред
- рдЬрдм рд╣рд╛рдореАрд▓реЗ рд╕рдореНрдмрдиреНрдзрд┐рдд рд░реЗрдХрд░реНрдб рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрдЫреМрдВ, рд╣рд╛рдореА рдХреБрдЮреНрдЬреАрджреНрд╡рд╛рд░рд╛ рд╢рдмреНрджрдХреЛрд╢рдмрд╛рдЯ рдирд┐рдХрд╛рд▓рд┐рдпреЛ рдкрд╛рда рд╕реНрдЯреНрд░рд┐рдЩрдХреЛ рд░реВрдкрдорд╛ред
- рд╣рд╛рдореАрд▓рд╛рдИ рдкрд╛рда рдЪрд╛рд╣рд┐рдиреНрдЫ рддрд╛рд▓рд┐рдХрд╛ рдкреНрд░рдХрд╛рд░ рдорд╛рдирдорд╛ рдмрджрд▓реНрдиреБрд╣реЛрд╕реН рд╡реНрдпрдХреНрддрд┐ (рдкреНрд░рддреНрдпреЗрдХ рддрд╛рд▓рд┐рдХрд╛рдХреЛ рд▓рд╛рдЧрд┐ рд╕рдорд╛рди рдирд╛рдордХреЛ рдкреНрд░рдХрд╛рд░ рд╕реНрд╡рдЪрд╛рд▓рд┐рдд рд░реВрдкрдорд╛ рд╕рд┐рд░реНрдЬрдирд╛ рд╣реБрдиреНрдЫ)ред
- рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рд╕реНрддрдореНрднрд╣рд░реВрдорд╛ рдЯрд╛рдЗрдк рдЧрд░рд┐рдПрдХреЛ рд░реЗрдХрд░реНрдб "рд╡рд┐рд╕реНрддрд╛рд░ рдЧрд░реНрдиреБрд╣реЛрд╕реН"
(...).*
.
json рд╢рдмреНрджрдХреЛрд╢
рддрд░ рд╣рд╛рдореАрд▓реЗ рдорд╛рдерд┐ рд▓рд╛рдЧреВ рдЧрд░реЗрдХреЛ рдпрд╕реНрддреЛ рдЪрд╛рд▓рд▓реЗ рдХрд╛рдо рдЧрд░реНрджреИрди рдпрджрд┐ рддреНрдпрд╣рд╛рдБ "рдХрд╛рд╕реНрдЯрд┐рдЩ" рдЧрд░реНрдирдХреЛ рд▓рд╛рдЧрд┐ рдХреБрдиреИ рд╕рдореНрдмрдиреНрдзрд┐рдд рддрд╛рд▓рд┐рдХрд╛ рдкреНрд░рдХрд╛рд░ рдЫреИрди рднрдиреЗред рдареНрдпрд╛рдХреНрдХреИ рдЙрд╕реНрддреИ рдЕрд╡рд╕реНрдерд╛ рдЙрддреНрдкрдиреНрди рд╣реБрдиреЗрдЫ, рд░ рдпрджрд┐ рд╣рд╛рдореАрд▓реЗ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрдиреЗ рдкреНрд░рдпрд╛рд╕ рдЧрд░реНрдиреЗрдЫреМрдВ CTE рдкрдЩреНрдХреНрддрд┐, "рд╡рд╛рд╕реНрддрд╡рд┐рдХ" рддрд╛рд▓рд┐рдХрд╛ рд╣реЛрдЗрди.
рдпрд╕ рдЕрд╡рд╕реНрдерд╛рдорд╛ рддрд┐рдиреАрд╣рд░реВрд▓реЗ рд╣рд╛рдореАрд▓рд╛рдИ рдорджреНрджрдд рдЧрд░реНрдиреЗрдЫрдиреН
...
, p AS ( -- ╤Н╤В╨╛ ╤Г╨╢╨╡ CTE
SELECT
*
FROM
person
WHERE
...
)
, dict AS (
SELECT
json_object( -- ╤В╨╡╨┐╨╡╤А╤М ╤Н╤В╨╛ ╤Г╨╢╨╡ json
array_agg(id)::text[]
, array_agg(row_to_json(p))::text[] -- ╨╕ ╨▓╨╜╤Г╤В╤А╨╕ json ╨┤╨╗╤П ╨║╨░╨╢╨┤╨╛╨╣ ╤Б╤В╤А╨╛╨║╨╕
)
FROM
p
)
SELECT
*
FROM
T
, LATERAL(
SELECT
*
FROM
json_to_record(
((TABLE dict) ->> author_id::text)::json -- ╨╕╨╖╨▓╨╗╨╡╨║╨╗╨╕ ╨╕╨╖ ╤Б╨╗╨╛╨▓╨░╤А╤П ╨║╨░╨║ json
) AS j(name text, birth_date date) -- ╨╖╨░╨┐╨╛╨╗╨╜╨╕╨╗╨╕ ╨╜╤Г╨╢╨╜╤Г╤О ╨╜╨░╨╝ ╤Б╤В╤А╤Г╨║╤В╤Г╤А╤Г
) j;
рдпреЛ рдзреНрдпрд╛рди рджрд┐рдиреБрдкрд░реНрдЫ рдХрд┐ рд▓рдХреНрд╖реНрдп рд╕рдВрд░рдЪрдирд╛рдХреЛ рд╡рд░реНрдгрди рдЧрд░реНрджрд╛, рд╣рд╛рдореА рд╕реНрд░реЛрдд рд╕реНрдЯреНрд░рд┐рдЩрдХрд╛ рд╕рдмреИ рдХреНрд╖реЗрддреНрд░рд╣рд░реВ рд╕реВрдЪреАрдмрджреНрдз рдЧрд░реНрди рд╕рдХреНрджреИрдиреМрдВ, рддрд░ рдХреЗрд╡рд▓ рддреА рдорд╛рддреНрд░ рдЬреБрди рд╣рд╛рдореАрд▓рд╛рдИ рд╡рд╛рд╕реНрддрд╡рдореИ рдЪрд╛рд╣рд┐рдиреНрдЫред рдпрджрд┐ рд╣рд╛рдореАрд╕рдБрдЧ "рдиреЗрдЯрд┐рдн" рддрд╛рд▓рд┐рдХрд╛ рдЫ рднрдиреЗ, рдпреЛ рдкреНрд░рдХрд╛рд░реНрдп рдкреНрд░рдпреЛрдЧ рдЧрд░реНрди рд░рд╛рдореНрд░реЛ рдЫ json_populate_record
.
рд╣рд╛рдореА рдЕрдЭреИ рдкрдирд┐ рдПрдХ рдкрдЯрдХ рд╢рдмреНрджрдХреЛрд╢ рдкрд╣реБрдБрдЪ, рддрд░ json-[de] рдХреНрд░рдорд┐рдХрд░рдг рд▓рд╛рдЧрддрд╣рд░реВ рдзреЗрд░реИ рдЙрдЪреНрдЪ рдЫрдиреН, рдпрд╕реИрд▓реЗ, рдпреЛ рд╡рд┐рдзрд┐ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрдиреБ рдЙрдЪрд┐рдд рдЫ рдХреЗрд╣рд┐ рдЕрд╡рд╕реНрдерд╛рд╣рд░реВрдорд╛ рдорд╛рддреНрд░ рдЬрдм "рдЗрдорд╛рдирджрд╛рд░" CTE рд╕реНрдХреНрдпрд╛рди рдЖрдлреИрд▓рд╛рдИ рдЦрд░рд╛рдм рджреЗрдЦрд╛рдЙрдБрдЫред
рдкрд░реАрдХреНрд╖рдг рдкреНрд░рджрд░реНрд╢рди
рддреНрдпрд╕реЛрднрдП, рд╣рд╛рдореАрд▓реЗ рдбреЗрдЯрд╛рд▓рд╛рдИ рд╢рдмреНрджрдХреЛрд╢рдорд╛ рдХреНрд░рдордмрджреНрдз рдЧрд░реНрдиреЗ рджреБрдИ рддрд░рд┐рдХрд╛рд╣рд░реВ рдкрд╛рдпреМрдВ - hstore/json_objectред рдердк рд░реВрдкрдорд╛, рдХреБрдЮреНрдЬреАрд╣рд░реВ рд░ рдорд╛рдирд╣рд░реВрдХреЛ рдПрд░реЗрд╣рд░реВ рдкрдирд┐ рджреБрдИ рддрд░рд┐рдХрд╛рдорд╛ рдЙрддреНрдкрдиреНрди рдЧрд░реНрди рд╕рдХрд┐рдиреНрдЫ, рдкрд╛рдардорд╛ рдЖрдиреНрддрд░рд┐рдХ рд╡рд╛ рдмрд╛рд╣реНрдп рд░реВрдкрд╛рдиреНрддрд░рдгрдХреЛ рд╕рд╛рде: array_agg(i::text) / array_agg(i)::text[].
рд╡рд┐рд╢реБрджреНрдз рд╕рд┐рдВрдереЗрдЯрд┐рдХ рдЙрджрд╛рд╣рд░рдг рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рд╡рд┐рднрд┐рдиреНрди рдкреНрд░рдХрд╛рд░рдХрд╛ рдХреНрд░рдорд┐рдХрд░рдгрдХреЛ рдкреНрд░рднрд╛рд╡рдХрд╛рд░рд┐рддрд╛ рдЬрд╛рдБрдЪ рдЧрд░реМрдВ - рдХреБрдЮреНрдЬреАрд╣рд░реВрдХреЛ рд╡рд┐рднрд┐рдиреНрди рд╕рдВрдЦреНрдпрд╛рд╣рд░реВ рдХреНрд░рдордмрджреНрдз рдЧрд░реНрдиреБрд╣реЛрд╕реН:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;
рдореВрд▓реНрдпрд╛рдЩреНрдХрди рд▓рд┐рдкрд┐: рдХреНрд░рдорд┐рдХрд░рдг
WITH T AS (
SELECT
*
, (
SELECT
regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
FROM
(
SELECT
array_agg(el) ea
FROM
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, $$ || (1 << v) || $$) i
)
TABLE dict
$$) T(el text)
) T
) et
FROM
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ORDER BY
1, 2
)
SELECT
v
, avg(et)::numeric(32,3)
FROM
T
GROUP BY
1
ORDER BY
1;
PostgreSQL 11 рдорд╛, рд▓рдЧрднрдЧ 2^12 рдХреБрдЮреНрдЬреАрд╣рд░реВрдХреЛ рд╢рдмреНрджрдХреЛрд╢ рдЖрдХрд╛рд░ рд╕рдореНрдо json рд▓рд╛рдИ рдХреНрд░рдордмрджреНрдз рдЧрд░реНрди рдХрдо рд╕рдордп рд▓рд╛рдЧреНрдЫред рдпрд╕ рдЕрд╡рд╕реНрдерд╛рдорд╛, рд╕рдмреИрднрдиреНрджрд╛ рдкреНрд░рднрд╛рд╡рдХрд╛рд░реА json_object рд░ "рдЖрдиреНрддрд░рд┐рдХ" рдкреНрд░рдХрд╛рд░ рд░реВрдкрд╛рдиреНрддрд░рдгрдХреЛ рд╕рдВрдпреЛрдЬрди рд╣реЛред array_agg(i::text)
.
рдЕрдм рдкреНрд░рддреНрдпреЗрдХ рдХреБрдЮреНрдЬреАрдХреЛ рдорд╛рди рео рдкрдЯрдХ рдкрдвреНрдиреЗ рдкреНрд░рдпрд╛рд╕ рдЧрд░реМрдВ - рдЖрдЦрд┐рд░, рдпрджрд┐ рддрдкрд╛рдЗрдБ рд╢рдмреНрджрдХреЛрд╢рдорд╛ рдкрд╣реБрдБрдЪ рдЧрд░реНрдиреБрд╣реБрдиреНрди рднрдиреЗ, рддреНрдпрд╕реЛ рднрдП рдпреЛ рдХрд┐рди рдЖрд╡рд╢реНрдпрдХ рдЫ?
рдореВрд▓реНрдпрд╛рдЩреНрдХрди рд▓рд┐рдкрд┐: рд╢рдмреНрджрдХреЛрд╢рдмрд╛рдЯ рдкрдвреНрджреИ
WITH T AS (
SELECT
*
, (
SELECT
regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
FROM
(
SELECT
array_agg(el) ea
FROM
dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
explain analyze
WITH dict AS (
SELECT
json_object(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, $$ || (1 << v) || $$) i
)
SELECT
(TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
FROM
generate_series(1, $$ || (1 << (v + 3)) || $$) i
$$) T(el text)
) T
) et
FROM
generate_series(0, 19) v
, LATERAL generate_series(1, 7) i
ORDER BY
1, 2
)
SELECT
v
, avg(et)::numeric(32,3)
FROM
T
GROUP BY
1
ORDER BY
1;
рд░ ... рдкрд╣рд┐рд▓реЗ рдиреИ рд▓рдЧрднрдЧ 2^6 рдХреБрдЮреНрдЬреАрд╣рд░реВрд╕рдБрдЧ, json рд╢рдмреНрджрдХреЛрд╢рдмрд╛рдЯ рдкрдвреНрджрд╛ рдзреЗрд░реИ рдкрдЯрдХ рд╣рд░рд╛рдЙрди рдерд╛рд▓реНрдЫ hstore рдмрд╛рдЯ рдкрдвреНрджреИ, jsonb рдХреЛ рд▓рд╛рдЧреА 2^9 рдорд╛ рд╣реБрдиреНрдЫред
рдЕрдиреНрддрд┐рдо рдирд┐рд╖реНрдХрд░реНрд╖:
- рдпрджрд┐ рддрдкрд╛рдЗрдБ рдпреЛ рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ рднрдиреЗ рдзреЗрд░реИ рджреЛрд╣реЛрд░рд┐рдиреЗ рд░реЗрдХрд░реНрдбрд╣рд░реВрд╕рдБрдЧ рд╕рд╛рдореЗрд▓ рд╣реБрдиреБрд╣реЛрд╕реН - рдпреЛ рддрд╛рд▓рд┐рдХрд╛рдХреЛ "рд╢рдмреНрджрдХреЛрд╢" рдкреНрд░рдпреЛрдЧ рдЧрд░реНрди рд░рд╛рдореНрд░реЛ рдЫ
- рдпрджрд┐ рддрдкрд╛рдИрдВрдХреЛ рд╢рдмреНрджрдХреЛрд╢ рдЕрдкреЗрдХреНрд╖рд┐рдд рдЫ рд╕рд╛рдиреЛ рд░ рддрдкрд╛рдИрд▓реЗ рдпрд╕рдмрд╛рдЯ рдзреЗрд░реИ рдкрдвреНрдиреБрд╣реБрдиреЗрдЫреИрди - рддрдкрд╛рдИрдВ json [b] рдкреНрд░рдпреЛрдЧ рдЧрд░реНрди рд╕рдХреНрдиреБрд╣реБрдиреНрдЫ
- рдЕрдиреНрдп рд╕рдмреИ рдХреЗрд╕рд╣рд░реВрдорд╛ hstore + array_agg(i::text) рдердк рдкреНрд░рднрд╛рд╡рдХрд╛рд░реА рд╣реБрдиреЗрдЫ
рд╕реНрд░реЛрдд: www.habr.com