我正在尝试编写一个查询，该查询从多个表中返回数据并将其聚合到一个嵌套的 JSON 字段中。我觉得这在 SqlServer 上会表现出色，但正如Brent Ozar在这篇文章中所写，Postgres 优化器将 CTE 查询隔离在一起。这迫使我在第一个 CTE 级别使用WHERE语句，否则每次都会加载整个数据集。那和我不太习惯的特定 JSON 函数让我想知道这是否可以更好地执行。
我尝试在没有 CTE 的情况下编写此代码，但不确定如何嵌套子查询。
我在这里缺少任何 postgres 技巧吗？这些指标有效吗？
输出如下所示：
[{
"item_property_id": 1001010,
"property_name": "aadb480d8716e52da33ed350b00d6cef",
"values": [
"1f64450fae03b127cf95f9b06fca4bca",
"9a6883b8a87a5028bf7dfc27412c2de8"
]
},{
"item_property_id": 501010,
"property_name": "e870e8d81e16ee46c75493856b4c6b66",
"values": [
"a6bed25b407c515bb8a55f2e239066ec",
"feb10299fd6408e0d37a8761e334c97a"
]
},{
"item_property_id": 1010,
"property_name": "f2d7b27c50a059d9337c949c13aa3396",
"values": [
"56674c1c3d66c832abf87b436a4fd095",
"ff88fe69f4438a6277c792faaf485368"
]
}]
这是生成模式和测试数据的脚本
--create schema
drop table if exists public.items;
drop table if exists public.items_properties;
drop table if exists public.items_properties_values;
create table public.items(
item_id integer primary key,
item_name varchar(250));
create table public.items_properties(
item_property_id serial primary key,
item_id integer,
property_name varchar(250));
create table public.items_properties_values(
item_property_value_id serial primary key,
item_property_id integer,
property_value varchar(250));
CREATE INDEX items_index
ON public.items USING btree
(item_id ASC NULLS LAST,item_name asc nulls last)
TABLESPACE pg_default;
CREATE INDEX properties_index
ON public.items_properties USING btree
(item_property_id ASC NULLS LAST,item_id asc nulls last,property_name asc nulls last)
TABLESPACE pg_default;
CREATE INDEX values_index
ON public.items_properties_values USING btree
(item_property_value_id ASC NULLS LAST,item_property_id asc nulls last,property_value asc nulls last)
TABLESPACE pg_default;
--insert dummy data
insert into public.items
SELECT generate_series(1,500000),md5(random()::text);
insert into public.items_properties (item_id,property_name)
SELECT item_id,md5(random()::text) from public.items;
insert into public.items_properties (item_id,property_name)
SELECT item_id,md5(random()::text) from public.items;
insert into public.items_properties (item_id,property_name)
SELECT item_id,md5(random()::text) from public.items;
insert into public.items_properties_values (item_property_id,property_value)
select item_property_id,md5(random()::text) from public.items_properties;
insert into public.items_properties_values (item_property_id,property_value)
select item_property_id,md5(random()::text) from public.items_properties;
--Query returned successfully in 22 secs 704 msec.
这是SQL命令
如果没有第三行的 where ，加载大约需要 15 秒。我知道这正在加载数千条记录，所以它可能表现得很好，但我真的很喜欢第二个意见。
with cte_items as (
select item_id,item_name from public.items
--where item_id between 1000 and 1010
),cte_properties as (
select ip.item_id,ip.item_property_id,ip.property_name from public.items_properties ip
inner join cte_items i on i.item_id=ip.item_id
),cte_values as (
select ipv.item_property_value_id,ipv.item_property_id,ipv.property_value from public.items_properties_values ipv
inner join cte_properties p on ipv.item_property_id=p.item_property_id
)
select i.item_id,i.item_name,json_agg(json_build_object('item_property_id',prop.item_property_id,'property_name',prop.property_name,'values',prop.values))
from cte_items i
left join (
select cp.item_id,cp.item_property_id,cp.property_name,json_agg(to_json(cv.property_value)) "values"
from cte_properties cp
left join ( select val.item_property_id,val.property_value from cte_values val ) cv on cv.item_property_id=cp.item_property_id
group by cp.item_id,cp.item_property_id,cp.property_name
) prop
on i.item_id=prop.item_id
group by i.item_id,i.item_name
