áá»áœááºá¯ááºááá¯á·ááẠâááá¯ážááŸááºážáá¯á¶áá±á«áºáááºâ PostgreSQL queries áá»á¬ážá á áœááºážáá±á¬ááºáááºááᯠááŒáŸáá·áºáááºááẠáá°áááááºážáá±á¬ áááºážáááºážáá»á¬ážááᯠáá±á·áá¬ááŒááºážá¡ááœáẠáááºááœáŸááºážáá¬ážáá±á¬ áá±á¬ááºážáá«ážá á®ážáá®ážáá»á¬ážááᯠáááºáááºáá¯ááºáá±á¬ááºáá«áááºá
ááŸá¬ážáá«ážáá±á¬ááŸááºáááºážááẠJOIN áá¡áááºááá¯á·áá±á¬ááºááŸááááá·áºáááºá Sisyphean ááẠáááºážáá»ááºážááŸá¯áá»á¬ážááœáẠáá«áááºááẠá¡áá¹ááá¬ááºááŸááá±á¬ JOIN ááŸáá·áº OR CTE ááŸáá·áº CTE áá»áááºáááºáá«á
JOIN ááᯠááááºáááŒáá¯ááºáá°ážááá¯á· ááááºááá¯ááºáá«áá²á·... :)
áá«áá±ááá·áº áááŒá¬ááááá¯áááá¯á áá±á¬ááºážááá¯áá»ááºá á¡á²áá«ááẠáááááá¬áᬠááá¯á¡áá»áá¯ážááŸááá¬áááºá áá«ááŒá±á¬áá·áº áá®áá±á· ááŒáá¯ážá á¬ážáááºá á¡áááºážá¡ááŒá áº-á¡áá»á¬ážá¡á¬ážááŒáá·áº JOIN ááᯠáááºááŸá¬ážááá¯ááºáá«á - á¡áááá¬ááºááá¯á¡áá¯á¶ážááŒá¯ááŒááºážá
PostgreSQL 12 ááŸá áááºá á¡á±á¬ááºááœááºáá±á¬áºááŒáá¬ážáá±á¬ á¡áá»áá¯á·áá±á¬á¡ááŒá±á¡áá±áá»á¬ážááẠá¡áááºážáááºááœá²ááŒá¬ážáá±á¬ááŒá±á¬áá·áº ááŒááºáá¯ááºáá±ážááá¯ááºáááº
áá¯á¶áá±ááá¯ááºáá±á¬ áá¯ááºááœááºáá á¹á ááºáž CTE . áá±á¬á·ááᯠáááºááŸááºááŒááºážááŒáá·áº á€á¡ááŒá¯á¡áá°ááᯠááŒááºááŒá±á¬ááºážááá¯ááºáááºáMATERIALIZED
.
á¡ááá·áºá¡áááºááŸááá±á¬ áá±á«áá¬ááá áºáá¯ááœáẠ"á¡áá»ááºá¡áááº" á¡áá»á¬ážá¡ááŒá¬áž
á¡ááœááºááŸááºáááºáá±á¬ á¡áá¯á¶ážáá»ááŸá¯áá¬áááºááᯠáá°ááŒáá«á
áá¯á· - á
á¬áááºážáá
áºáá¯ááŒáááẠááá¯á¡ááºáááºá
25.01 | ÐваМПв Ð.Ð. | ÐПЎгПÑПвОÑÑ ÐŸÐ¿ÐžÑаМОе МПвПгП алгПÑОÑЌа.
22.01 | ÐваМПв Ð.Ð. | ÐапОÑаÑÑ ÑÑаÑÑÑ ÐœÐ° ХабÑ: Ð¶ÐžÐ·ÐœÑ Ð±ÐµÐ· JOIN.
20.01 | ÐеÑÑПв Ð.Ð. | ÐПЌПÑÑ ÐŸÐ¿ÑОЌОзОÑПваÑÑ Ð·Ð°Ð¿ÑПÑ.
18.01 | ÐваМПв Ð.Ð. | ÐапОÑаÑÑ ÑÑаÑÑÑ ÐœÐ° ХабÑ: JOIN Ñ ÑÑеÑПЌ ÑаÑпÑÐµÐŽÐµÐ»ÐµÐœÐžÑ ÐŽÐ°ÐœÐœÑÑ
.
16.01 | ÐеÑÑПв Ð.Ð. | ÐПЌПÑÑ ÐŸÐ¿ÑОЌОзОÑПваÑÑ Ð·Ð°Ð¿ÑПÑ.
á ááá¹ááááá¹áá¬ááœááºá á¡áá¯ááºá á¬áá±ážááá¬áá»á¬ážááᯠáá»áœááºá¯ááºááá¯á·áá¡ááœá²á·á¡á ááºážá¡ááœááºážááŸá áááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážááŒá¬ážááœáẠá¡áá®á¡áá»áŸ ááœá²áá±áá±ážááá·áºáá±á¬áºáááºáž áááºááœá±á·ááœááºáá°á á¡áá¯ááºáá»á¬ážááᯠá ááºážáááºážá¡á áá»áŸáá»áŸáá ááá·áºáááºáá¬ážáá±á¬ áá°áá»á¬ážá០áá¬áá«áááºá - á¡áááºáá®ážáá»ááºážáá¬ááá»á¬ážá០âá á®áá¶ááá·áºááœá²ááŸá¯ááŸâ á¡áááºá¡á±á¬áẠááá¯á·ááá¯áẠâáááºáááá¯ááºáá¬áá»á¬ážááŸâ á¡áá (áá±á·áá¬áá¯á¶ážáááºáá°áá»á¬ážá áá®ááá¯ááºáá¬áá»á¬ážá áá¬ážáááºáááºážá ...)á
áá° áááá ááŸááá±á¬ áá»áœááºá¯ááºááá¯á·áá¡ááœá²á·á¡á
ááºážááœááºá á
á¬áá±ážááᬠá¡áá±á¬áẠáá áᬠ(áá»á¬ážáá±á¬á¡á¬ážááŒáá·áº áááºážáá«ážáááº) ááẠáááá»áá±á¬áá»á±á¬áºááŒá±áááºáááºáá°ááá¯ááºážá¡ááœáẠá¡áá¯ááºáá»á¬ážááᯠáááºááŸááºáá±ážáááºááᯠáááºáá¶ááŒáá«á
áá¯á·á
áá¬ááºááœáŸááºážáá®ážá ááº
-- ÑПÑÑÑЎМОкО
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);
áá®ážááŒá¬áž executor á¡ááœáẠáá±á¬ááºáá¯á¶áž áá¯ááºáá±á¬ááºá áᬠ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 ááŸá¬ááœá±áááºáᬠááŒá¯áá¯ááºáá¬ážááẠ- output task áá áºáá¯á á®á¡ááœááºá áá«áá±ááá·áº á¡á²áá®áá¬áá²á·áá»á®áá²á·á¡áá²ááŸá¬ áá«ááá¯á·áááááºá 20 áá²ááœá¬áááºá - á€á¡ááááá¬ááᯠá¡áá¯á¶ážááŒá¯ááẠááŒá áºááá¯ááºáá«ááá¬ážá
hstore-á¡áááá¬ááº
á¡ááœáá·áºáá±á¬ááºážáá°ááŒáá«á
áá¯á·
CREATE EXTENSION hstore
á€áá±á¬á·ááá¯á¡áá¯á¶ážááŒá¯á áá¯ááºáá°ááá¯ááºá á±áááºá¡ááœáẠá á¬áá±ážáá°á ID ááŸáá·áº áá°áá¡áááºááᯠá¡áááá¬ááºááœáẠááá·áºááœááºážááẠááá¯á¡ááºáááº-
-- ÑПÑЌОÑÑеЌ ÑелевÑÑ Ð²ÑбПÑкÑ
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;
áá°áá»á¬ážá¡ááŒá±á¬ááºáž ááááºážá¡áá»ááºá¡áááºáááŸááá±ážááœáẠáá¯á¶ážá
áœá²áá²á·áááºá á¡áá»ááẠá á ááá¯áááºážááŒá®áž áá±áá¬áááºááŒááºáž á á áá»á±á¬á·áááºážáááºá! âáá±á«áá¬áâ á¡ááŒááºá á€ááááºáá»á¬ážáááŸáááẠáá»áœááºá¯ááºááá¯á·ááᯠáá°áá®áá±ážáá²á·ááá·áºá¡áá¬ááŸá¬á á¡á
á¯ááá¯ááºááŸááºáááºážááŒááºáááºááá°ááŒááºážá á
á¬ážááœá²áá±á«áºááá± pass áá
áºáá¯áááºážááá¯áá¯á¶ážááŒá®áž = 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 ááẠfull person table entry á¡ááœáẠalias á¡ááŒá Ạá¡áááºážá¡áá»ááºážááá¯á·ááᯠá ááºážáá±ážá á±áá
- ဠá¡áá¶ááœááºážáá¬ážáá±á¬ array áá»á¬ážááᯠááŒááºáááºááá·áºááœááºážáá²á·áááºá áááºážááᯠhstore á¡áááá¬ááºááœáẠáááºááá¯ážáá»á¬ážá array á¡ááŒá áºáá¬ážááẠá á¬áá¬ážááŒáá¯ážáá»á¬áž (person[]::text[])á
- áááºá ááºááŸááºáááºážáá áºáá¯áááŸááá±á¬á¡áá«á á¡áááá¬ááºá០áá±á¬á·ááŒáá·áº ááœá²áá¯ááºáááºá á á¬áá¬áž string á¡ááŒá áºá
- á á¬áá¬ážááá¯áááºá ááá¬ážá¡áá»áá¯ážá¡á á¬ážáááºááá¯ážá¡ááŒá áºááá¯á·ááŒá±á¬ááºážáá«á áá°áá áºáŠáž (ááá¬ážáá áºáá¯á á®á¡ááœáẠáá°áá®áá±á¬á¡áááºáá áºáá»áá¯ážááᯠá¡ááá¯á¡áá»á±á¬ááºáááºáá®ážáááº)á
- ááá¯ááºááœááºážáá¬ážáá±á¬ ááŸááºáááºážááᯠá¡áá¯á¶ážááŒá¯á áá±á¬áºáá¶áá»á¬ážááá¯á· "áá»á²á·"
(...).*
.
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]serialization áá¯ááºáá»á ááááºááẠá¡ááœááºááŒáá·áºáá¬ážáááºáááá¯á·ááŒá±á¬áá·áºá "ááá¯ážáá¬áž" CTE Scan ááẠáá°á·ááá¯ááºáá° ááá¯ááá¯ážáá¬áá±á¬á¡áá«ááœááºáᬠá€áááºážáááºážááᯠá¡áá¯á¶ážááŒá¯ááẠáá»áá¯ážááŒá±á¬ááºážáá®áá»á±á¬áºáá«áááºá
á áœááºážáá±á¬ááºáááºá ááºážáááºááŒááºážá
ááá¯á·ááŒá±á¬áá·áºá áá»áœááºá¯ááºááá¯á·ááẠáá±áá¬ááᯠá¡áááá¬ááºáá áºáá¯á¡ááŒá Ạá á®á á áºááẠáááºážáááºážááŸá áºáá¯ááŸááááºá 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 ááá¯á· serialization ááẠá¡áá»áááºáááºážáááºá. á€ááá
á¹á
ááœááºá á¡áááá±á¬ááºáá¯á¶ážááŸá¬ json_object ááŸáá·áº "internal" á¡áá»áá¯ážá¡á
á¬ážááŒá±á¬ááºážáá²ááŒááºážááá±á«ááºážá
ááºááŸá¯ááŒá
áºáááºá 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) ááá¯áááá±á¬ááºáá«áááá·áºáááºá
source: www.habr.com