ืืืจ ืคืึธืจืืขืฆื ืื ืกืขืจืืข ืคืื โโโโืึทืจืืืงืืขื ืืขืืจืืึท ืฆื ืื ืืขืจื ืขื ืคืื ืงืืืื-ืืืงืื ื ืืืขืื ืฆื ืคึฟืึทืจืืขืกืขืจื ืื ืคืึธืจืฉืืขืืื ื ืคืื "ืคึผืึธื ืขื ืคึผืฉืื" ืคึผืึธืกืืืจืขืกืงื ืงืืืืจืื:
ืื ืืืืกื ื ืืฉื ืืจืึทืืื ืึทื ืืื ืืึธื ื ืื ืืื JIN ืึทืืื ืคืื ... :)
ืึธืืขืจ ืึธืคื ืึธื ืขืก, ืื ืืงืฉื ืืืจื ืก ืืืืก ืฆื ืืืื ืืืืืืืืง ืืขืจ ืคึผืจืึธืืืงืืืื ืืื ืืื ืืื. ืึทืืื ืืืึทื ื ืืืจ ืืืขืื ืคึผืจืืืืจื ืืึทืงืืืขื ืืึทืคืจืืึทืขื ืคืื ืืืื-ืืื ืืขื ืกืืืืข JOIN - ื ืืฆื ืึท ืืืขืจืืขืจืืื.
ืกืืึทืจืืื ื ืืื PostgreSQL 12, ืขืืืขืืข ืคืื โโโโืื ืกืืืืึทืืืึธื ืก ืืืกืงืจืืืื ืืื ืื ืงืขื ืืืื ืจืืคึผืจืึทืืืกื ืึท ืืืกื ืึทื ืืขืจืฉ ืืืืึทื ืคืื . ืืขื ืึธืคึผืคืืจืื ื ืงืขื ืขื ืืืื ืจืืืืขืจืืื ืืืจื ืกืคึผืขืฆืืคืืฆืืจื ืื ืฉืืืกื
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 ืคืึทืจืฉืืืขื ืข - ืืื ืขืก ืืขืืืขื ืฆื ื ืืฆื ืืขื ืืืืกื?
ืืกืืึธืจืข-ืืืขืจืืขืจืืื
ืืื ืก ื ืขืืขื ืืืึทืืข ืฆื ืืืฉืขื ืขืจืืื ืึท "ืืืขืจืืขืจืืื" ืฉืืืกื ืืืขืจื:
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 ืืื ืึทื ืึทืืืึทืก ืฆื ืื ืคืื ืืขื ืืฉ ืืืฉ ืคึผืึธืืืฆืืข ืืึผื ืึธืื ืึตืื ืึดืืึธืืึฐื ืึท ืึทืื ึฐืึถืขื.
- ืืขื ืื ืืขื ืืข ืคืื โโืจืขืงืึธืจืืื ืืก ืืื ืืขืืืขื ืจืืงืึทืกื ืฆื ืึท ืืขื ืืข ืคืื โโืืขืงืกื ืกืืจืื ืืก (ืืขื ืืฉ [] :: ืืขืงืกื []) ืฆื ืฉืืขืื ืขืก ืืื ืื 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_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 ืงืื, ืืืืขื ืขื ืคืื ืึท ืืืฉืกืึธื ืืืขืจืืขืจืืื ืืืืื ืฆื ืคืึทืจืืืจื ืขืืืขืืข ืืึธื ืืืืขื ืขื ืคึฟืื hstore, ืคึฟืึทืจ jsonb ืืขืจ ืืขืืืืงืขืจ ืืึทืคึผืึทื ื ืืื 2 ^ 9.
ืืขืฆืืข ืืกืงื ืืช:
- ืืืื ืืืจ ืืึทืจืคึฟื ืฆื ืืึธื ืขืก ืคืึทืจืืื ืื ืืื ืงืืืคื ืจืืคึผืืืื ื ืจืขืงืึธืจืืก - ืขืก ืืื ืืขืกืขืจ ืฆื ื ืืฆื "ืืืขืจืืขืจืืื" ืคืื ืื ืืืฉ
- ืืืื ืืืื ืืืขืจืืขืจืืื ืืื ืืขืจืืืึทืจื ืงืืืื ืืื ืืืจ ืืืขื ื ืืฉื ืืืืขื ืขื ืคืื ืคืื ืืื - ืืืจ ืงืขื ืขื ื ืืฆื json[b]
- ืืื ืืืข ืื ืืขืจืข ืคืืื hstore + array_agg (ืืื:: ืืขืงืกื) ืืืขื ืืืื ืืขืจ ืขืคืขืงืืืื
ืืงืืจ: www.habr.com