此查詢持續約 30 秒：
WITH cte AS(SELECT t.id, to_json (row) json
FROM public.test t, LATERAL (SELECT * FROM public.test2 t2 WHERE t.id = t2.test_id) row)
UPDATE test t
SET json = cte.json
FROM cte
WHERE cte.id = t.id
"Update on test t (cost=55185.61..96669.49 rows=0 width=0) (actual time=26952.797..26952.804 rows=0 loops=1)"
" -> Hash Join (cost=55185.61..96669.49 rows=150572 width=50) (actual time=1415.264..4256.365 rows=150571 loops=1)"
" Hash Cond: (t_1.id = t2.test_id)"
" -> Seq Scan on test t_1 (cost=0.00..28237.36 rows=545736 width=14) (actual time=0.026..349.467 rows=355580 loops=1)"
" -> Hash (cost=50068.46..50068.46 rows=150572 width=150) (actual time=1359.280..1359.285 rows=150571 loops=1)"
" Buckets: 32768 Batches: 8 Memory Usage: 3717kB"
" -> Hash Join (cost=10007.87..50068.46 rows=150572 width=150) (actual time=339.426..1191.612 rows=150571 loops=1)"
" Hash Cond: (t.id = t2.test_id)"
" -> Seq Scan on test t (cost=0.00..28237.36 rows=545736 width=14) (actual time=0.015..492.247 rows=355580 loops=1)"
" -> Hash (cost=5184.72..5184.72 rows=150572 width=136) (actual time=225.551..225.553 rows=150571 loops=1)"
" Buckets: 32768 Batches: 8 Memory Usage: 3420kB"
" -> Seq Scan on test2 t2 (cost=0.00..5184.72 rows=150572 width=136) (actual time=0.017..79.663 rows=150572 loops=1)"
"Planning Time: 0.931 ms"
"Execution Time: 26954.723 ms"
另一個沒有 cte 的查詢持續了太多時間（仍在等待完成）：
UPDATE test t
SET json = (SELECT to_json(row) FROM (SELECT * FROM test2 t2 WHERE t.id = t2.test_id LIMIT 1) row)
我想在 test2 中創建每一行的 json 并用它更新測驗表。為什么第一個查詢比第二個更好？沒有LATERAL怎么寫？
架構：
CREATE TABLE test(
id SERIAL PRIMARY KEY,
json TEXT
);
CREATE TABLE test2(
id SERIAL PRIMARY KEY,
test_id INTEGER,
a TEXT,
b TEXT,
c TEXT,
d TEXT,
e TEXT,
f TEXT,
g TEXT,
h TEXT,
i TEXT,
j TEXT,
k TEXT,
l TEXT,
m TEXT
);
uj5u.com熱心網友回復：
您將目標表與第二個表多次連接。
一次就夠了：
UPDATE test t
SET json = to_json(t2)
FROM public.test2 as t2
WHERE t2.test_id = t.id;
如果要從 JSON 中排除某些列，請使用jsonb（無論如何這是 JSON 的推薦資料型別）：
UPDATE test t
SET json = to_jsonb(t2) - 'a' - 'b'
FROM public.test2 as t2
WHERE t2.test_id = t.id;
如果要重命名某些列，可以使用派生表：
UPDATE test t
SET json = to_json(t2)
FROM (
select test_id,
a as xyz,
b as abc
from public.test2
) as as t2
WHERE t2.test_id = t.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/519897.html