|
Можно ли оптимизировать данный запрос с json
#39373605
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Melkij,
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
CREATE TABLE "matrix_table" (
"id" INTEGER NOT NULL'',
"products" UNKNOWN NULL,
INDEX "" ("id"),
PRIMARY KEY ("id")
);
CREATE TABLE "products_table" (
"id" INTEGER NOT NULL'',
"components" UNKNOWN NULL,
"name" VARCHAR(255) NOT NULL,
INDEX "" ("id"),
PRIMARY KEY ("id")
);
CREATE TABLE "components_table" (
"id" INTEGER NOT NULL'',
"name" VARCHAR(255) NOT NULL,
INDEX "" ("id"),
PRIMARY KEY ("id")
);
matrix_table example row = id, [{"cost": 674, "product_id": 34}, {"cost": 716, "product_id": 43}]
products_table example row = id, [{"amount": 895, "component_id": 87}, {"amount": 77, "component_id": 51}], name
components_table example row = id, name
1.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
EXPLAIN (ANALYZE) SELECT mj.row_id, mj.json_id, mj.product_id, pj.json_id, pj.component_id, amount, cost, pj.pname, components_table.name FROM getProductsJson() pj
JOIN components_table ON pj.component_id = components_table.id
JOIN getMatrixJson() mj ON pj.row_id = mj.product_id
"Hash Join (cost=28.75..128.75 rows=2500 width=90) (actual time=28.169..107.725 rows=266456 loops=1)"
" Hash Cond: (mj.product_id = pj.row_id)"
" -> Function Scan on getmatrixjson mj (cost=0.25..10.25 rows=1000 width=20) (actual time=11.227..11.988 rows=5173 loops=1)"
" -> Hash (cost=22.25..22.25 rows=500 width=74) (actual time=16.926..16.926 rows=5173 loops=1)"
" Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 481kB"
" -> Hash Join (cost=3.50..22.25 rows=500 width=74) (actual time=10.259..14.363 rows=5173 loops=1)"
" Hash Cond: (pj.component_id = components_table.id)"
" -> Function Scan on getproductsjson pj (cost=0.25..10.25 rows=1000 width=52) (actual time=10.152..11.047 rows=5173 loops=1)"
" -> Hash (cost=2.00..2.00 rows=100 width=26) (actual time=0.094..0.094 rows=100 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 14kB"
" -> Seq Scan on components_table (cost=0.00..2.00 rows=100 width=26) (actual time=0.011..0.053 rows=100 loops=1)"
"Planning time: 0.271 ms"
"Execution time: 135.678 ms"
2.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
EXPLAIN (ANALYZE) Select mj.row_id, mj.json_id, mj.product_id, pj.json_id, pj.component_id, amount, cost, pj.pname, components_table.name FROM
(select id as row_id,
ordinality as json_id,
(value->>'component_id')::int as component_id,
(value->>'amount')::int as amount,
name as pname
from products_table
cross join jsonb_array_elements(components) WITH ORDINALITY) pj
JOIN components_table ON pj.component_id = components_table.id
JOIN
(select id as row_id,
ordinality as json_id,
(value->>'product_id')::int as product_id,
(value->>'cost')::int as cost
from matrix_table
cross join jsonb_array_elements(products) WITH ORDINALITY) mj
ON pj.row_id = mj.product_id
"Hash Join (cost=346.77..46657.78 rows=1020000 width=65) (actual time=9.156..503.600 rows=266456 loops=1)"
" Hash Cond: (products_table.id = ((jsonb_array_elements_1.value ->> 'product_id'::text))::integer)"
" -> Hash Join (cost=3.25..389.25 rows=10000 width=73) (actual time=0.142..10.818 rows=5173 loops=1)"
" Hash Cond: (((jsonb_array_elements.value ->> 'component_id'::text))::integer = components_table.id)"
" -> Nested Loop (cost=0.00..211.00 rows=10000 width=51) (actual time=0.044..4.676 rows=5173 loops=1)"
" -> Seq Scan on products_table (cost=0.00..11.00 rows=100 width=720) (actual time=0.013..0.197 rows=100 loops=1)"
" -> Function Scan on jsonb_array_elements (cost=0.00..1.00 rows=100 width=40) (actual time=0.019..0.029 rows=52 loops=100)"
" -> Hash (cost=2.00..2.00 rows=100 width=26) (actual time=0.074..0.074 rows=100 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 14kB"
" -> Seq Scan on components_table (cost=0.00..2.00 rows=100 width=26) (actual time=0.010..0.035 rows=100 loops=1)"
" -> Hash (cost=216.02..216.02 rows=10200 width=44) (actual time=8.974..8.974 rows=5173 loops=1)"
" Buckets: 16384 Batches: 1 Memory Usage: 639kB"
" -> Nested Loop (cost=0.00..216.02 rows=10200 width=44) (actual time=0.020..4.803 rows=5173 loops=1)"
" -> Seq Scan on matrix_table (cost=0.00..12.02 rows=102 width=758) (actual time=0.004..0.056 rows=102 loops=1)"
" -> Function Scan on jsonb_array_elements jsonb_array_elements_1 (cost=0.00..1.00 rows=100 width=40) (actual time=0.017..0.028 rows=51 loops=102)"
"Planning time: 0.567 ms"
"Execution time: 527.591 ms"
|
|
|
