原sql
SELECT
t1.id,
t1.column1,
t1.column2,
t1.column3,
array_to_string(ARRAY (SELECT tt.column1||‘:’||tt.column2 FROM table2 tt WHERE tt.foreign_key=t1.id),‘;’) as column4,
array_to_string(ARRAY (SELECT tt.column1||‘,’||tt.column2 FROM table3 tt WHERE tt.foreign_key=info.id),‘;’) as column5,
array_to_string(ARRAY (SELECT tt.column3||‘:’||tt.column2 FROM table4 tt WHERE tt.foreign_key=info.id),‘;’) as column6
FROM table1 t1
优化后sql
SELECT
t1.id,
t1.column1,
t1.column2,
t1.column3,
t2.column4,
t3.column5,
t4.column6
FROM table1 t1
left join (select tt.foreign_key,string_agg(tt.column1||‘:’||tt.column2,‘;’) as column4 from table2 tt group by tt.foreign_key) t2 on t1.id = t2.foreign_key
left join (select tt.foreign_key,string_agg(tt.column1||‘:’||tt.column2,‘;’) as column4 from table2 tt group by tt.foreign_key) t3 on t1.id = t3.foreign_key
left join (select tt.foreign_key,string_agg(tt.column1||‘:’||tt.column2,‘;’) as column4 from table2 tt group by tt.foreign_key) t4 on t1.id = t4.foreign_key
优化前1W条数据查询需要200秒
优化后1W条数据查询只需1秒