问题
数据库: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
我没有来自以前服务器的分析。之前服务器的设置和现在的差不多。
重复索引
对于初学者来说,复制大部分索引当然无济于事。
read
正如查询计划中的大量 ' 所示,您的设置正在努力解决缓存不足的问题。在你做任何其他事情之前先放弃欺骗。这些只会增加成本。一般难度
Queries with
ORDER BY
on some columns, filter on others and and a smallLIMIT
are always tricky. See:With insufficient or misleading statistics Postgres may reckon it can just traverse an index that fits the sort order and find the few rows required soon enough, but then it turns out to be a long journey. Oftentimes it helps to increase the statistics target on key columns, or tinker with some other settings. See:
Better index
You have this index:
And this one even three times (which adds to the problem):
One of the latter triple gets used, but the query plan reveals the problem with it:
Meaning, over half a million index tuples are read from the index, but filtered after visiting the heap because they failed the condition
(date IS NULL)
. One big waste of time.The one index on table
dieting_daymenu
you really need:That should solve the main problem.
"Ugly
OR
"With the above index, the next problem may or may not go away already:
If not, you could split up that "ugly
OR
" into three separateSELECT
andUNION
them together. Each can then be optimized individually, often leading to dramatically improved performance. See:(It's an odd query to begin with, that has to check in any / all of the three tables for
user_id = 55
. Smells like a design problem, and you should only have to check in one ...)In this case, depending on the selectivity of
user_id
in each table, you ideally have one ore more of these indexes (instead of the one suggested above):For the 1st subselect:
dieting_daymenu (user_id, position, name) WHERE date IS NULL
For the 2nd subselect:
dieting_daymenu (diet_plan_id, position, name) WHERE date IS NULL
dieting_dietplan(user_id, id)
For the 3rd subselect:
dieting_daymenu (patient_id, position, name) WHERE date IS NULL
patients_patient(user_id, id)