Postgresql
PostgreSQL 10 優化慢查詢性能
我有以下查詢：
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我對查詢優化不太熟悉。歡迎任何建議。至少你能指出我必須在哪裡創建額外的索引嗎？先感謝您。
您花費大量時間打包子查詢“x”，只是為了從中提取 150 個單值結果。您可以將“x”上的子查詢移出連接並進入選擇列表嗎？或者，也許，進入橫向連接？這樣，它就可以提取並打包要返回的特定行的數據。
如果沒有針對所有表、索引和約束的 CREATE 語句，就很難提出一個確切的語法供您使用，因為我沒有簡單的方法來測試它。
此外，所有這些左連接真的有必要嗎？如果你不需要對所有隱含的 NULL 做任何事情，也許你可以去掉一些’left’。
我會說問題是對索引掃描的嚴重錯誤估計
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 得到正確的估計，它可能會選擇嵌套循環連接並且速度更快。