āĻāĻŽāĻ°āĻž "āĻāĻĒāĻžāĻ¤āĻĻā§āĻˇā§āĻāĻŋāĻ¤ā§ āĻ¸āĻšāĻ" PostgreSQL āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋāĻ° āĻāĻžāĻ°ā§āĻ¯āĻāĻžāĻ°āĻŋāĻ¤āĻž āĻāĻ¨ā§āĻ¨āĻ¤ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸ā§āĻŦāĻ˛ā§āĻĒ-āĻĒāĻ°āĻŋāĻāĻŋāĻ¤ āĻāĻĒāĻžāĻ¯āĻŧāĻā§āĻ˛āĻŋāĻ° āĻ āĻ§ā§āĻ¯āĻ¯āĻŧāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻ¤ā§āĻ¸āĻ°ā§āĻā§āĻā§āĻ¤ āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻā§āĻ˛āĻŋāĻ° āĻ¸āĻŋāĻ°āĻŋāĻāĻāĻŋ āĻāĻžāĻ˛āĻŋāĻ¯āĻŧā§ āĻ¯āĻžāĻā§āĻāĻŋ:
āĻŦāĻŋāĻ°āĻ˛ āĻ°ā§āĻāĻ°ā§āĻĄ āĻ¯ā§āĻāĻĻāĻžāĻ¨ā§āĻ° āĻŽāĻžāĻāĻāĻžāĻ¨ā§ āĻĒā§āĻāĻā§ āĻ¯āĻžāĻŦā§ Sisyphean āĻ ā§āĻ¯āĻžāĻ°ā§ āĻ¯ā§āĻāĻĻāĻžāĻ¨ āĻāĻ°ā§āĻ¨ āĻā§āĻˇāĻ¤āĻŋāĻāĻ° JOIN āĻāĻŦāĻ OR CTE CTE-āĻ āĻ¯ā§āĻ āĻĻāĻŋāĻ¨
āĻāĻžāĻŦāĻŦā§āĻ¨ āĻ¨āĻž āĻ¯ā§ āĻāĻŽāĻŋ āĻāĻ¯āĻŧā§āĻ¨ āĻāĻ°āĻ¤ā§ āĻā§āĻŦ āĻāĻāĻāĻž āĻĒāĻāĻ¨ā§āĻĻ āĻāĻ°āĻŋ āĻ¨āĻž... :)
āĻ¤āĻŦā§ āĻĒā§āĻ°āĻžāĻ¯āĻŧāĻļāĻ āĻāĻāĻŋ āĻāĻžāĻĄāĻŧāĻžāĻ, āĻ āĻ¨ā§āĻ°ā§āĻ§āĻāĻŋ āĻāĻāĻŋāĻ° āĻā§āĻ¯āĻŧā§ āĻāĻ˛ā§āĻ˛ā§āĻāĻ¯ā§āĻā§āĻ¯āĻāĻžāĻŦā§ āĻŦā§āĻļāĻŋ āĻāĻ¤ā§āĻĒāĻžāĻĻāĻ¨āĻļā§āĻ˛ āĻšāĻ¯āĻŧā§ āĻāĻ ā§āĨ¤ āĻ¤āĻžāĻ āĻāĻ āĻāĻŽāĻ°āĻž āĻā§āĻˇā§āĻāĻž āĻāĻ°āĻŦ āĻ¸āĻŽā§āĻĒāĻĻ-āĻ¨āĻŋāĻŦāĻŋāĻĄāĻŧ āĻ¯ā§āĻāĻĻāĻžāĻ¨ āĻĨā§āĻā§ āĻĒāĻ°āĻŋāĻ¤ā§āĻ°āĻžāĻŖ āĻĒāĻžāĻ¨ - āĻāĻāĻāĻŋ āĻ āĻāĻŋāĻ§āĻžāĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĨ¤
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;
āĻŦā§āĻ¯āĻā§āĻ¤āĻŋ āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻ¤āĻĨā§āĻ¯ āĻĒā§āĻ°āĻžāĻĒā§āĻ¤āĻŋāĻ° āĻāĻ¨ā§āĻ¯ āĻŦā§āĻ¯āĻ¯āĻŧ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ 2 āĻā§āĻŖ āĻāĻŽ āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŦāĻ 7 āĻā§āĻŖ āĻāĻŽ āĻĄā§āĻāĻž āĻĒāĻĄāĻŧāĻž! "āĻļāĻŦā§āĻĻāĻāĻžāĻ¨ā§āĻĄāĻžāĻ°" āĻāĻžāĻĄāĻŧāĻžāĻ, āĻ¯āĻž āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻ āĻĢāĻ˛āĻžāĻĢāĻ˛āĻā§āĻ˛āĻŋ āĻ
āĻ°ā§āĻāĻ¨ āĻāĻ°āĻ¤ā§ āĻ¸āĻžāĻšāĻžāĻ¯ā§āĻ¯ āĻāĻ°ā§āĻāĻŋāĻ˛ āĻ¤āĻž āĻšāĻ˛ āĻŦāĻžāĻ˛ā§āĻ āĻ°ā§āĻāĻ°ā§āĻĄ āĻĒā§āĻ¨āĻ°ā§āĻĻā§āĻ§āĻžāĻ° āĻā§āĻŦāĻŋāĻ˛ āĻĨā§āĻā§ āĻāĻāĻāĻŋ āĻāĻāĻ āĻĒāĻžāĻ¸ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ = 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 āĻĒā§āĻ°ā§āĻŖ āĻŦā§āĻ¯āĻā§āĻ¤āĻŋ āĻā§āĻŦāĻŋāĻ˛ āĻāĻ¨ā§āĻā§āĻ°āĻŋāĻ° āĻāĻāĻāĻŋ āĻāĻĒāĻ¨āĻžāĻŽ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻāĻŦāĻ āĻ¤āĻžāĻĻā§āĻ° āĻāĻāĻāĻŋ āĻ ā§āĻ¯āĻžāĻ°ā§ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤.
- āĻāĻ āĻ°ā§āĻāĻ°ā§āĻĄāĻŋāĻ āĻāĻ° āĻ ā§āĻ¯āĻžāĻ°ā§ recast āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ āĻā§āĻā§āĻ¸āĻ āĻ¸ā§āĻā§āĻ°āĻŋāĻāĻā§āĻ˛āĻŋāĻ° āĻāĻāĻāĻŋ āĻ ā§āĻ¯āĻžāĻ°ā§āĻ¤ā§ (āĻŦā§āĻ¯āĻā§āĻ¤āĻŋ[]::āĻā§āĻā§āĻ¸āĻ[]) āĻāĻāĻŋāĻā§ 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)
.
āĻāĻāĻ¨ āĻāĻ¸ā§āĻ¨ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻā§āĻ° āĻŽāĻžāĻ¨ 8 āĻŦāĻžāĻ° āĻĒāĻĄāĻŧāĻžāĻ° āĻā§āĻˇā§āĻāĻž āĻāĻ°āĻŋ - āĻ¸āĻ°ā§āĻŦā§āĻĒāĻ°āĻŋ, āĻāĻĒāĻ¨āĻŋ āĻ¯āĻĻāĻŋ āĻ āĻāĻŋāĻ§āĻžāĻ¨āĻāĻŋ āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻ¨āĻž āĻāĻ°ā§āĻ¨, āĻ¤āĻŦā§ āĻāĻāĻŋāĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻā§āĻ¨?
āĻŽā§āĻ˛ā§āĻ¯āĻžāĻ¯āĻŧāĻ¨ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ: āĻāĻāĻāĻŋ āĻ āĻāĻŋāĻ§āĻžāĻ¨ āĻĨā§āĻā§ āĻĒāĻĄāĻŧāĻž
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