PostgreSQL 10优化慢速查询性能
PostgreSQL 10优化慢速查询性能
提问于 2019-05-07 10:39:47
我有以下查询：
select i.id as id,
i.ts_updated as updated,
i."name" as name,
cast(attr.data_norm as text) as data_norm,
cast(_59.json_agg as text) as _59,
attr.num_ports as num_ports,
cast(_58.json_agg as text) as _58,
cast(_60.json_agg as text) as _60,
attr.frequency as frequency,
cast(attr.data as text) as data,
attr.r_ref as r_ref,
attr.frequency_hz as frequency_hz,
cast(tags_array as text) as tags
from bo_instance i
left join attrib_touchstone attr on i.id=attr.bo_instance_id
left join ( select x_boi_tag.bo_instance_id as instance_id,
json_agg(tag."name" order by tag."name") as tags_array
from x_boi_tag
left join tag on x_boi_tag.tag_id=tag.id
where tag.is_deleted = false
group by instance_id ) t on t.instance_id=i.id
left join ( select x.bo_instance_id,
json_agg(json_build_object('name', boAttr."name", 'value', v."name")) as value_list
from x_ia_value_list x
left join bo_attribute_value v on v.id=x.bo_attribute_value_id
left join bo_class_attribute boAttr on v.bo_class_attribute_id = boAttr.id
group by x.bo_instance_id ) val on val.bo_instance_id = i.id
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')
from json_array_elements(val.value_list)
where value #>> '{name}' = 'freq_units') as _59 on true
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')
from json_array_elements(val.value_list)
where value #>> '{name}' = 'parameter_type') as _58 on true
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')
from json_array_elements(val.value_list)
where value #>> '{name}' = 'format') as _60 on true
where i.is_deleted=false
and i.bo_class_id=34
and true
order by i.id desc limit 150执行了22秒，在我看来，这是相当缓慢的。
以下是查询计划：
Limit (cost=869494.67..870365.34 rows=150 width=285) (actual time=25585.374..25612.329 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, ((attr.data_norm)::text), (((json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))))::text), attr.num_ports, (((json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))))::text), (((json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))))::text), attr.frequency, ((attr.data)::text), attr.r_ref, attr.frequency_hz, ((t.tags_array)::text)
Buffers: shared hit=38064, temp read=41731 written=63158
-> Nested Loop Left Join (cost=869494.67..7900133.26 rows=1211245 width=285) (actual time=25585.372..25605.884 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, (attr.data_norm)::text, ((json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))))::text, attr.num_ports, ((json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))))::text, ((json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))))::text, attr.frequency, (attr.data)::text, attr.r_ref, attr.frequency_hz, (t.tags_array)::text
Buffers: shared hit=37674, temp read=41731 written=63158
-> Nested Loop Left Join (cost=869493.16..5998478.61 rows=1211245 width=1707) (actual time=25585.330..25589.575 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list, (json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))), (json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[])))
Buffers: shared hit=37674, temp read=41731 written=63158
-> Nested Loop Left Join (cost=869491.65..4133161.31 rows=1211245 width=1675) (actual time=25585.311..25588.171 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list, (json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[])))
Buffers: shared hit=37674, temp read=41731 written=63158
-> Merge Left Join (cost=869490.14..2267844.01 rows=1211245 width=1643) (actual time=25585.257..25586.503 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list
Inner Unique: true
Merge Cond: (i.id = val.bo_instance_id)
Buffers: shared hit=37674, temp read=41731 written=63158
-> Merge Left Join (cost=1046.86..1380457.54 rows=1211245 width=1611) (actual time=9.411..10.418 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array
Inner Unique: true
Merge Cond: (i.id = t.instance_id)
Buffers: shared hit=3771
-> Gather Merge (cost=1000.88..1377380.87 rows=1211245 width=1579) (actual time=7.744..14.986 rows=150 loops=1)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4154
-> Nested Loop Left Join (cost=0.85..1236573.12 rows=504685 width=1579) (actual time=1.129..1.527 rows=80 loops=3)
Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz
Buffers: shared hit=4154
Worker 0: actual time=0.016..0.200 rows=42 loops=1
Buffers: shared hit=182
Worker 1: actual time=0.016..0.228 rows=48 loops=1
Buffers: shared hit=208
-> Parallel Index Scan Backward using bo_instance_pkey on public.bo_instance i (cost=0.43..536630.17 rows=504685 width=66) (actual time=0.883..0.953 rows=80 loops=3)
Output: i.id, i.search_text, i.bo_class_id, i.ts_created, i.ts_updated, i.updated_by_user, i.updated_by_process, i.name, i.is_deleted
Filter: ((NOT i.is_deleted) AND (i.bo_class_id = 34))
Rows Removed by Filter: 1155
Buffers: shared hit=3192
Worker 0: actual time=0.007..0.037 rows=42 loops=1
Buffers: shared hit=13
Worker 1: actual time=0.007..0.042 rows=48 loops=1
Buffers: shared hit=15
-> Index Scan using idx_attrib_touchstone_bo_instance_id on public.attrib_touchstone attr (cost=0.43..1.38 rows=1 width=1521) (actual time=0.005..0.006 rows=1 loops=240)
Output: attr.id, attr.ts_created, attr.ts_updated, attr.updated_by_user, attr.updated_by_process, attr.bo_instance_id, attr.bo_class_id, attr.num_ports, attr.r_ref, attr.frequency, attr.frequency_hz, attr.data, attr.data_norm
Index Cond: (i.id = attr.bo_instance_id)
Buffers: shared hit=962
Worker 0: actual time=0.002..0.002 rows=1 loops=42
Buffers: shared hit=169
Worker 1: actual time=0.002..0.002 rows=1 loops=48
Buffers: shared hit=193
-> Sort (cost=45.98..46.43 rows=180 width=40) (actual time=1.664..1.665 rows=1 loops=1)
Output: t.tags_array, t.instance_id
Sort Key: t.instance_id DESC
Sort Method: quicksort Memory: 41kB
Buffers: shared hit=7
-> Subquery Scan on t (cost=32.18..39.24 rows=180 width=40) (actual time=0.735..1.577 rows=191 loops=1)
Output: t.tags_array, t.instance_id
Buffers: shared hit=7
-> GroupAggregate (cost=32.18..37.44 rows=180 width=40) (actual time=0.733..1.489 rows=191 loops=1)
Output: x_boi_tag.bo_instance_id, json_agg(tag.name ORDER BY tag.name)
Group Key: x_boi_tag.bo_instance_id
Buffers: shared hit=7
-> Sort (cost=32.18..33.18 rows=401 width=14) (actual time=0.715..0.814 rows=462 loops=1)
Output: x_boi_tag.bo_instance_id, tag.name
Sort Key: x_boi_tag.bo_instance_id
Sort Method: quicksort Memory: 51kB
Buffers: shared hit=7
-> Hash Join (cost=4.19..14.84 rows=401 width=14) (actual time=0.111..0.521 rows=462 loops=1)
Output: x_boi_tag.bo_instance_id, tag.name
Inner Unique: true
Hash Cond: (x_boi_tag.tag_id = tag.id)
Buffers: shared hit=7
-> Seq Scan on public.x_boi_tag (cost=0.00..9.44 rows=444 width=16) (actual time=0.009..0.144 rows=462 loops=1)
Output: x_boi_tag.id, x_boi_tag.bo_instance_id, x_boi_tag.tag_id, x_boi_tag.ts_created, x_boi_tag.ts_updated, x_boi_tag.updated_by_user, x_boi_tag.updated_by_process
Buffers: shared hit=5
-> Hash (cost=3.03..3.03 rows=93 width=14) (actual time=0.094..0.095 rows=82 loops=1)
Output: tag.name, tag.id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=2
-> Seq Scan on public.tag (cost=0.00..3.03 rows=93 width=14) (actual time=0.005..0.059 rows=82 loops=1)
Output: tag.name, tag.id
Filter: (NOT tag.is_deleted)
Rows Removed by Filter: 22
Buffers: shared hit=2
-> Sort (cost=868443.28..871232.88 rows=1115840 width=40) (actual time=25575.835..25575.910 rows=156 loops=1)
Output: val.value_list, val.bo_instance_id
Sort Key: val.bo_instance_id DESC
Sort Method: external merge Disk: 172768kB
Buffers: shared hit=33903, temp read=41731 written=63158
-> Subquery Scan on val (cost=633959.56..695332.83 rows=1115840 width=40) (actual time=10203.379..23976.330 rows=1209626 loops=1)
Output: val.value_list, val.bo_instance_id
Buffers: shared hit=33903, temp read=14082 written=14082
-> GroupAggregate (cost=633959.56..684174.43 rows=1115840 width=40) (actual time=10203.378..23208.688 rows=1209626 loops=1)
Output: x.bo_instance_id, json_agg(json_build_object('name', boattr.name, 'value', v.name))
Group Key: x.bo_instance_id
Buffers: shared hit=33903, temp read=14082 written=14082
-> Sort (cost=633959.56..643026.28 rows=3626687 width=23) (actual time=10203.356..11984.566 rows=3626608 loops=1)
Output: x.bo_instance_id, boattr.name, v.name
Sort Key: x.bo_instance_id
Sort Method: external merge Disk: 112656kB
Buffers: shared hit=33903, temp read=14082 written=14082
-> Hash Left Join (cost=11.09..90071.01 rows=3626687 width=23) (actual time=0.181..6299.137 rows=3626608 loops=1)
Output: x.bo_instance_id, boattr.name, v.name
Inner Unique: true
Hash Cond: (v.bo_class_attribute_id = boattr.id)
Buffers: shared hit=33903
-> Hash Left Join (cost=3.51..80289.63 rows=3626687 width=22) (actual time=0.073..3898.956 rows=3626608 loops=1)
Output: x.bo_instance_id, v.name, v.bo_class_attribute_id
Inner Unique: true
Hash Cond: (x.bo_attribute_value_id = v.id)
Buffers: shared hit=33899
-> Seq Scan on public.x_ia_value_list x (cost=0.00..70163.87 rows=3626687 width=16) (actual time=0.006..1242.323 rows=3626608 loops=1)
Output: x.id, x.bo_attribute_value_id, x.bo_instance_id, x.ts_created, x.ts_updated, x.updated_by_user, x.updated_by_process
Buffers: shared hit=33897
-> Hash (cost=2.67..2.67 rows=67 width=22) (actual time=0.060..0.061 rows=83 loops=1)
Output: v.name, v.id, v.bo_class_attribute_id
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=2
-> Seq Scan on public.bo_attribute_value v (cost=0.00..2.67 rows=67 width=22) (actual time=0.003..0.031 rows=83 loops=1)
Output: v.name, v.id, v.bo_class_attribute_id
Buffers: shared hit=2
-> Hash (cost=5.59..5.59 rows=159 width=17) (actual time=0.103..0.103 rows=159 loops=1)
Output: boattr.name, boattr.id
Buckets: 1024 Batches: 1 Memory Usage: 17kB
Buffers: shared hit=4
-> Seq Scan on public.bo_class_attribute boattr (cost=0.00..5.59 rows=159 width=17) (actual time=0.003..0.050 rows=159 loops=1)
Output: boattr.name, boattr.id
Buffers: shared hit=4
-> Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=150)
Output: json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))
-> Function Scan on pg_catalog.json_array_elements (cost=0.00..1.50 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=150)
Output: json_array_elements.value
Function Call: json_array_elements(val.value_list)
Filter: ((json_array_elements.value #>> '{name}'::text[]) = 'freq_units'::text)
Rows Removed by Filter: 2
-> Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=150)
Output: json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))
-> Function Scan on pg_catalog.json_array_elements json_array_elements_1 (cost=0.00..1.50 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=150)
Output: json_array_elements_1.value
Function Call: json_array_elements(val.value_list)
Filter: ((json_array_elements_1.value #>> '{name}'::text[]) = 'parameter_type'::text)
Rows Removed by Filter: 2
-> Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.040..0.040 rows=1 loops=150)
Output: json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))
-> Function Scan on pg_catalog.json_array_elements json_array_elements_2 (cost=0.00..1.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=150)
Output: json_array_elements_2.value
Function Call: json_array_elements(val.value_list)
Filter: ((json_array_elements_2.value #>> '{name}'::text[]) = 'format'::text)
Rows Removed by Filter: 2
Planning time: 1.027 ms
Execution time: 25728.499 ms我不太熟悉查询优化。欢迎任何建议。至少您能指出我必须在哪里创建额外的索引吗？提前谢谢你。
回答 2
Database Administration用户
发布于 2019-05-07 17:28:36
您正在花费大量的时间打包子查询"x"，只是为了从其中提取150个单值结果。您能否将子查询从联接中移出"x“，并放入选择列表中？或者，加入一个横向连接？这样，它就可以提取并打包它将要返回的特定行的数据。
如果没有针对所有表、索引和约束的CREATE语句，就很难为您提供确切的语法供您使用，因为我没有简单的方法来测试它。
另外，是否所有剩下的连接都是必要的？如果你不需要对所有隐含的NULLs做任何事情，也许你可以放弃一些‘左边的’。
Database Administration用户
发布于 2019-05-10 16:52:02
我想说的是，问题在于public.bo_instance上索引扫描的严重错误估计：
-> Parallel Index Scan Backward using bo_instance_pkey on public.bo_instance i
(cost=0.43..536630.17 rows=504685 width=66)
(actual time=0.883..0.953 rows=80 loops=3)
Filter: ((NOT i.is_deleted) AND (i.bo_class_id = 34))
Rows Removed by Filter: 1155
Buffers: shared hit=3192统计数字似乎相距甚远。这张桌子似乎也很臃肿，我会先跑
VACUUM (FULL) public.bo_instance;然后
VACUUM (ANALYZE) public.bo_instance;来解决这个问题。
如果PostgreSQL得到了正确的估计，它可能会选择一个嵌套的循环连接，并且速度更快。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/237537
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号