问题
数据库:x86_64-pc-linux-gnu 上的 PostgreSQL 13.8 (Debian 13.8-0+deb11u1),由 gcc (Debian 10.2.1-6) 10.2.1 20210110,64 位编译从 DigitalOcean 迁移到强大的专用服务器后,我们注意到应用程序某些部分的性能下降。尝试真空、分析、重建索引。由于锁定表,未尝试完全真空。
桌子尺寸
dieting_dietplan = 119936
dieting_portion = 189444545
dieting_meal = 41467323
dieting_dayevent = 18225193
dieting_daymenu = 4024864
patients_patient = 181563
表
病人: Table "public.patients_patient"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
user_id | integer | | | | plain | |
Indexes:
"patients_patient_pkey" PRIMARY KEY, btree (id)
"patients_patient_e8701ad4" btree (user_id)
部分:
Table "public.dieting_portion"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
position | integer | | not null | | plain | |
analyzer_data | jsonb | | not null | | extended | |
product_id | integer | | not null | | plain | |
product_name | character varying(100) | | not null | | extended | |
amount | numeric(8,2) | | not null | | main | |
amount_label | character varying(100) | | not null | | extended | |
raw | boolean | | not null | | plain | |
meal_id | uuid | | not null | | plain | |
user_id | integer | | | | plain | |
Indexes:
"dieting_portion_pkey" PRIMARY KEY, btree (id)
"dieting_por_positio_f064a0_idx" btree ("position", product_name)
"dieting_por_positio_f064a0_idx_ccnew" btree ("position", product_name)
"dieting_portion_6894eaee" btree (meal_id)
"dieting_portion_6894eaee_ccnew" btree (meal_id)
"dieting_portion_e8701ad4" btree (user_id)
"dieting_portion_e8701ad4_ccnew" btree (user_id)
"dieting_portion_f5625f45" btree (product_name)
"dieting_portion_f5625f45_ccnew" btree (product_name)
"dieting_portion_pkey_ccnew" UNIQUE, btree (id)
"dieting_portion_position_f183396b_uniq" btree ("position")
"dieting_portion_position_f183396b_uniq_ccnew" btree ("position")
"dieting_portion_product_id_1ac06c8f_uniq" btree (product_id)
"dieting_portion_product_id_1ac06c8f_uniq_ccnew" btree (product_id)
"dieting_portion_product_name_fe8ddc5f_like" btree (product_name varchar_pattern_ops)
"dieting_portion_product_name_fe8ddc5f_like_ccnew" btree (product_name varchar_pattern_ops)
一顿饭:
Table "public.dieting_meal"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
position | integer | | not null | | plain | |
deleted_at | timestamp with time zone | | | | plain | |
analyzer_data | jsonb | | not null | | extended | |
name | character varying(200) | | not null | | extended | |
recipe | text | | not null | | extended | |
meal_type | smallint | | not null | | plain | |
portion_count | smallint | | not null | | plain | |
product_names | jsonb | | not null | | extended | |
meal_attrs | jsonb | | not null | | extended | |
day_event_id | uuid | | not null | | plain | |
parent_id | uuid | | | | plain | |
user_id | integer | | | | plain | |
source | uuid | | | | plain | |
checksum | character varying(32) | | not null | | extended | |
Indexes:
"dieting_meal_pkey" PRIMARY KEY, btree (id)
"dieting_mea_positio_75f21e_idx" btree ("position", name)
"dieting_mea_positio_75f21e_idx_ccnew" btree ("position", name)
"dieting_meal_1ad1fc7d" btree (day_event_id)
"dieting_meal_1ad1fc7d_ccnew" btree (day_event_id)
"dieting_meal_226190d9" btree (checksum)
"dieting_meal_226190d9_ccnew" btree (checksum)
"dieting_meal_6be37982" btree (parent_id)
"dieting_meal_6be37982_ccnew" btree (parent_id)
"dieting_meal_8546dfa4" btree (portion_count)
"dieting_meal_8546dfa4_ccnew" btree (portion_count)
"dieting_meal_a9c6ef03" btree (meal_attrs)
"dieting_meal_a9c6ef03_ccnew" btree (meal_attrs)
"dieting_meal_b068931c" btree (name)
"dieting_meal_b068931c_ccnew" btree (name)
"dieting_meal_checksum_46f2e953_like" btree (checksum varchar_pattern_ops)
"dieting_meal_checksum_46f2e953_like_ccnew" btree (checksum varchar_pattern_ops)
"dieting_meal_e8701ad4" btree (user_id)
"dieting_meal_e8701ad4_ccnew" btree (user_id)
"dieting_meal_name_1d47f664_like" btree (name varchar_pattern_ops)
"dieting_meal_name_1d47f664_like_ccnew" btree (name varchar_pattern_ops)
"dieting_meal_pkey_ccnew" UNIQUE, btree (id)
"dieting_meal_position_a8c5fbb7_uniq" btree ("position")
"dieting_meal_position_a8c5fbb7_uniq_ccnew" btree ("position")
日间活动:
Table "public.dieting_dayevent"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
analyzer_data | jsonb | | not null | | extended | |
ref_eng | smallint | | | | plain | |
ref_cep | smallint | | | | plain | |
ref_pep | smallint | | | | plain | |
ref_fep | smallint | | | | plain | |
nutrient_refs | jsonb | | not null | | extended | |
name | character varying(100) | | not null | | extended | |
start_at | time without time zone | | not null | | plain | |
end_at | time without time zone | | | | plain | |
event_type | smallint | | not null | | plain | |
meal_energy_share | smallint | | not null | | plain | |
day_menu_id | uuid | | not null | | plain | |
user_id | integer | | | | plain | |
base_day_event_id | uuid | | | | plain | |
Indexes:
"dieting_dayevent_pkey" PRIMARY KEY, btree (id)
"dieting_day_start_a_d8efc8_idx" btree (start_at, end_at)
"dieting_day_start_a_d8efc8_idx_ccnew" btree (start_at, end_at)
"dieting_dayevent_38e26cbe" btree (day_menu_id)
"dieting_dayevent_38e26cbe_ccnew" btree (day_menu_id)
"dieting_dayevent_base_day_event_id_13afee34" btree (base_day_event_id)
"dieting_dayevent_base_day_event_id_13afee34_ccnew" btree (base_day_event_id)
"dieting_dayevent_e8701ad4" btree (user_id)
"dieting_dayevent_e8701ad4_ccnew" btree (user_id)
"dieting_dayevent_event_type_2258b92e" btree (event_type)
"dieting_dayevent_event_type_2258b92e_ccnew" btree (event_type)
"dieting_dayevent_pkey_ccnew" UNIQUE, btree (id)
"dieting_dayevent_start_at_6f8df923" btree (start_at)
"dieting_dayevent_start_at_6f8df923_ccnew" btree (start_at)
日间菜单:
Table "public.dieting_daymenu"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
position | integer | | | | plain | |
analyzer_data | jsonb | | not null | | extended | |
ref_eng | smallint | | | | plain | |
ref_cep | smallint | | | | plain | |
ref_pep | smallint | | | | plain | |
ref_fep | smallint | | | | plain | |
nutrient_refs | jsonb | | not null | | extended | |
name | character varying(100) | | not null | | extended | |
awakening_at | time without time zone | | | | plain | |
sleeping_at | time without time zone | | | | plain | |
first_meal_after_awakening | smallint | | not null | | plain | |
last_meal_before_sleeping | smallint | | not null | | plain | |
diet_plan_id | uuid | | | | plain | |
user_id | integer | | | | plain | |
date | date | | | | plain | |
base_day_menu_id | uuid | | | | plain | |
is_bought | boolean | | not null | | plain | |
is_in_userbase | boolean | | not null | | plain | |
Indexes:
"dieting_daymenu_pkey" PRIMARY KEY, btree (id)
"dieting_day_positio_df3d34_idx" btree ("position", name)
"dieting_daymenu_b068931c" btree (name)
"dieting_daymenu_base_day_menu_id_3d8de1de" btree (base_day_menu_id)
"dieting_daymenu_date_a2e9ba0a" btree (date)
"dieting_daymenu_date_index" btree (date) WHERE date IS NULL
"dieting_daymenu_date_index_2" btree (date)
"dieting_daymenu_date_index_3" btree (date, user_id)
"dieting_daymenu_e8701ad4" btree (user_id)
"dieting_daymenu_f3738446" btree (diet_plan_id)
"dieting_daymenu_name_094b5f38_like" btree (name varchar_pattern_ops)
"dieting_daymenu_position_name_index" btree ("position", name)
"dieting_daymenu_position_name_index2" btree ("position", name)
饮食计划:
Table "public.dieting_dietplan"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | not null | | plain | |
created_at | timestamp with time zone | | | | plain | |
updated_at | timestamp with time zone | | | | plain | |
deleted_at | timestamp with time zone | | | | plain | |
ref_eng | smallint | | | | plain | |
ref_cep | smallint | | | | plain | |
ref_pep | smallint | | | | plain | |
ref_fep | smallint | | | | plain | |
nutrient_refs | jsonb | | not null | | extended | |
name | character varying(200) | | not null | | extended | |
description | text | | not null | | extended | |
nutrefset_id | uuid | | | | plain | |
patient_id | uuid | | | | plain | |
user_id | integer | | | | plain | |
copying_day_menus | boolean | | not null | | plain | |
copying_diet_plan | boolean | | not null | | plain | |
Indexes:
"dieting_dietplan_pkey" PRIMARY KEY, btree (id)
"dieting_die_created_c3b833_idx" btree (created_at DESC)
"dieting_dietplan_9f065c57" btree (patient_id)
"dieting_dietplan_b068931c" btree (name)
"dieting_dietplan_c06d7788" btree (nutrefset_id)
"dieting_dietplan_e8701ad4" btree (user_id)
"dieting_dietplan_name_7114c662_like" btree (name varchar_pattern_ops)
设置
无效索引(也许这是问题所在)
SELECT pg_class.relname
FROM pg_class, pg_index
WHERE pg_index.indisvalid = false
AND pg_index.indexrelid = pg_class.oid;
relname
------------------------------
pg_toast_6861016_index_ccnew
pg_toast_6861058_index_ccnew
询问
由网络框架 Django 自动生成。在恢复数据库之前它工作正常。作为最后的手段,我想更改查询代码:SELECT
"dieting_daymenu"."id",
"dieting_daymenu"."created_at",
"dieting_daymenu"."updated_at",
"dieting_daymenu"."position",
"dieting_daymenu"."analyzer_data",
"dieting_daymenu"."date",
"dieting_daymenu"."base_day_menu_id",
"dieting_daymenu"."ref_eng",
"dieting_daymenu"."ref_cep",
"dieting_daymenu"."ref_pep",
"dieting_daymenu"."ref_fep",
"dieting_daymenu"."nutrient_refs",
"dieting_daymenu"."user_id",
"dieting_daymenu"."diet_plan_id",
"dieting_daymenu"."name",
"dieting_daymenu"."awakening_at",
"dieting_daymenu"."sleeping_at",
"dieting_daymenu"."first_meal_after_awakening",
"dieting_daymenu"."last_meal_before_sleeping",
"dieting_daymenu"."is_bought",
"dieting_daymenu"."is_in_userbase",
"dieting_dietplan"."id",
"dieting_dietplan"."created_at",
"dieting_dietplan"."updated_at",
"dieting_dietplan"."deleted_at",
"dieting_dietplan"."ref_eng",
"dieting_dietplan"."ref_cep",
"dieting_dietplan"."ref_pep",
"dieting_dietplan"."ref_fep",
"dieting_dietplan"."nutrient_refs",
"dieting_dietplan"."patient_id",
"dieting_dietplan"."user_id",
"dieting_dietplan"."nutrefset_id",
"dieting_dietplan"."name",
"dieting_dietplan"."description",
"dieting_dietplan"."copying_day_menus",
"dieting_dietplan"."copying_diet_plan"
FROM
"dieting_daymenu"
LEFT OUTER JOIN "dieting_dietplan" ON (
"dieting_daymenu"."diet_plan_id" = "dieting_dietplan"."id"
)
LEFT OUTER JOIN "patients_patient" ON (
"dieting_dietplan"."patient_id" = "patients_patient"."id"
)
WHERE
(
(
"dieting_dietplan"."user_id" = 55
OR "patients_patient"."user_id" = 55
OR "dieting_daymenu"."user_id" = 55
)
AND "dieting_daymenu"."date" IS NULL
)
ORDER BY
"dieting_daymenu"."position" ASC,
"dieting_daymenu"."name" ASC
LIMIT
10;
查询说明
随机地,有时它会下载更快,有时会超过一分钟:
第一次尝试: https: //explain.dalibo.com/plan/gd09b5g1abbgcb8g
第二次尝试:https: //explain.dalibo.com/plan/c666e8df1b863dga
第三次尝试:https: //explain.dalibo.com/plan/57f00d9eh500197f
更新问题
下面解释分析缓冲区:
Limit (cost=1.27..11262.25 rows=10 width=2082) (actual time=166744.058..213933.918 rows=10 loops=1)
Buffers: shared hit=1269034 read=442242 dirtied=54
I/O Timings: read=210465.272
-> Nested Loop Left Join (cost=1.27..2028103.71 rows=1801 width=2082) (actual time=166744.056..213933.910 rows=10 loops=1)
Filter: ((dieting_dietplan.user_id = 55) OR (patients_patient.user_id = 55) OR (dieting_daymenu.user_id = 55))
Rows Removed by Filter: 134581
Buffers: shared hit=1269034 read=442242 dirtied=54
I/O Timings: read=210465.272
-> Nested Loop Left Join (cost=0.85..1848414.36 rows=182054 width=2082) (actual time=0.044..213298.617 rows=134591 loops=1)
Buffers: shared hit=757143 read=441447 dirtied=53
I/O Timings: read=210420.153
-> Index Scan using dieting_daymenu_position_name_index2 on dieting_daymenu (cost=0.43..1671952.27 rows=182054 width=1888) (actual time=0.033..211640.389 rows=134591 loops=1)
Filter: (date IS NULL)
Rows Removed by Filter: 553628
Buffers: shared hit=243472 read=440290 dirtied=53
I/O Timings: read=209578.602
-> Index Scan using dieting_dietplan_pkey on dieting_dietplan (cost=0.42..0.97 rows=1 width=194) (actual time=0.011..0.011 rows=1 loops=134591)
Index Cond: (id = dieting_daymenu.diet_plan_id)
Buffers: shared hit=513671 read=1157
I/O Timings: read=841.551
-> Index Scan using patients_patient_pkey on patients_patient (cost=0.42..0.97 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=134591)
Index Cond: (id = dieting_dietplan.patient_id)
Buffers: shared hit=511890 read=795
I/O Timings: read=45.118
Planning:
Buffers: shared hit=33
Planning Time: 0.525 ms
Execution Time: 213933.964 ms
我没有来自以前服务器的分析。之前服务器的设置和现在的差不多。