หลายเดือนก่อน
คุณใช้งานไปแล้วมากกว่า 6000 ครั้ง แต่คุณสมบัติที่มีประโยชน์อย่างหนึ่งที่อาจไม่มีใครสังเกตเห็นคือ เบาะแสเชิงโครงสร้างซึ่งมีลักษณะดังนี้:
ฟังพวกเขาแล้วคำขอของคุณจะ “ราบรื่นและเนียน” 🙂
แต่จริงๆ แล้ว มีหลายสถานการณ์ที่ทำให้คำขอช้าและกินทรัพยากรมาก เป็นเรื่องปกติและสามารถรับรู้ได้จากโครงสร้างและข้อมูลของแผน.
ในกรณีนี้ นักพัฒนาแต่ละคนไม่จำเป็นต้องมองหาตัวเลือกการเพิ่มประสิทธิภาพด้วยตนเอง โดยอาศัยประสบการณ์ของเขาเพียงอย่างเดียว เราสามารถบอกเขาได้ว่าเกิดอะไรขึ้นที่นี่ อะไรอาจเป็นเหตุผล และ วิธีการแก้ไขปัญหา. นั่นคือสิ่งที่เราทำ
มาดูกรณีเหล่านี้ให้ละเอียดยิ่งขึ้น - มีวิธีกำหนดอย่างไรและให้คำแนะนำอะไรบ้าง
เพื่อให้คุณสามารถดื่มด่ำกับหัวข้อได้ดียิ่งขึ้นคุณสามารถฟังบล็อกที่เกี่ยวข้องก่อนได้
ดัชนี "ขีดล่าง" สี่แยกดัชนี (BitmapAnd) การรวมดัชนี (BitmapOr) เราอ่านสิ่งที่ไม่จำเป็นมากมาย โต๊ะเบาบาง อ่านจาก "ตรงกลาง" ของดัชนี ซีทีอี × ซีทีอี สลับไปยังดิสก์ (เขียนอุณหภูมิ) สถิติที่ไม่เกี่ยวข้อง "บางอย่างผิดพลาด"
#1: ดัชนี “การจัดเรียงต่ำกว่า”
เมื่อไร
แสดงใบแจ้งหนี้ล่าสุดสำหรับลูกค้า "LLC Kolokolchik"
วิธีการระบุ
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
แนะนำ
ดัชนีที่ใช้ ขยายด้วยฟิลด์เรียงลำดับ.
ตัวอย่าง:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
คุณจะสังเกตเห็นได้ทันทีว่ามีการลบบันทึกมากกว่า 100 รายการออกจากดัชนี ซึ่งจากนั้นจะถูกจัดเรียงทั้งหมด และเหลือเพียงรายการเดียวเท่านั้น
การแก้ไข:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
แม้แต่ในกลุ่มตัวอย่างดั้งเดิม - เร็วขึ้น 8.5 เท่า และอ่านน้อยลง 33 เท่า. ยิ่งคุณมี "ข้อเท็จจริง" สำหรับแต่ละค่ามากเท่าใด ผลที่ได้ก็จะยิ่งชัดเจนมากขึ้นเท่านั้น fk
.
ฉันทราบว่าดัชนีดังกล่าวจะทำงานเป็นดัชนี "คำนำหน้า" ไม่แย่ไปกว่าเมื่อก่อนสำหรับข้อความค้นหาอื่นด้วย fk
โดยเรียงลำดับตาม pk
ไม่มีและไม่มี (คุณสามารถอ่านเพิ่มเติมเกี่ยวกับเรื่องนี้ได้
#2: จุดตัดดัชนี (BitmapAnd)
เมื่อไร
แสดงข้อตกลงทั้งหมดสำหรับลูกค้า “LLC Kolokolchik” ซึ่งสรุปในนามของ “NAO Buttercup”
วิธีการระบุ
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan
แนะนำ
สร้าง ดัชนีคอมโพสิต ตามฟิลด์จากทั้งสองต้นฉบับหรือขยายอันใดอันหนึ่งที่มีอยู่ด้วยฟิลด์จากอันที่สอง
ตัวอย่าง:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
การแก้ไข:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
ผลตอบแทนที่นี่น้อยกว่า เนื่องจาก Bitmap Heap Scan ค่อนข้างมีประสิทธิภาพในตัวเอง แต่อย่างไรก็ตาม เร็วขึ้น 7 เท่า และอ่านน้อยลง 2.5 เท่า.
#3: รวมดัชนี (BitmapOr)
เมื่อไร
แสดงคำขอ “พวกเรา” ที่เก่าที่สุดหรือคำขอที่ยังไม่ได้มอบหมายสำหรับการประมวลผล 20 คำขอแรก โดยให้ความสำคัญกับคำขอของคุณเป็นหลัก
วิธีการระบุ
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan
แนะนำ
ที่จะใช้ ยูเนี่ยน [ทั้งหมด] เพื่อรวมแบบสอบถามย่อยสำหรับแต่ละเงื่อนไข OR
ตัวอย่าง:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
การแก้ไข:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
เราใช้ประโยชน์จากความจริงที่ว่าได้รับบันทึกที่จำเป็นทั้งหมด 20 รายการทันทีในบล็อกแรก ดังนั้นบล็อกที่สองซึ่งมี Bitmap Heap Scan ที่ "แพงกว่า" จึงไม่ได้ดำเนินการด้วยซ้ำ - ท้ายที่สุด เร็วขึ้น 22 เท่า อ่านน้อยลง 44 เท่า!
เรื่องราวโดยละเอียดเพิ่มเติมเกี่ยวกับวิธีการเพิ่มประสิทธิภาพนี้ โดยใช้ตัวอย่างเฉพาะ สามารถอ่านได้ในบทความ
PostgreSQL Antipatterns: JOIN และ OR ที่เป็นอันตราย иPostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา" .รุ่นทั่วไป เรียงลำดับการเลือกตามหลายคีย์ (และไม่ใช่แค่คู่ const/NULL) ที่ถูกกล่าวถึงในบทความ
SQL HowTo: เขียน while-loop โดยตรงในแบบสอบถาม หรือ "Elementary three-way" .
#4: เราอ่านสิ่งที่ไม่จำเป็นมากมาย
เมื่อไร
ตามกฎแล้ว มันเกิดขึ้นเมื่อคุณต้องการ "แนบตัวกรองอื่น" กับคำขอที่มีอยู่แล้ว
“และคุณไม่มีอันเดียวกันแต่ ด้วยกระดุมมุก? " ภาพยนตร์เรื่อง "แขนเพชร"
ตัวอย่างเช่น การแก้ไขงานด้านบน แสดงคำขอ "สำคัญ" ที่เก่าที่สุด 20 รายการแรกสำหรับการประมวลผล โดยไม่คำนึงถึงวัตถุประสงค์
วิธีการระบุ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
แนะนำ
สร้างความเชี่ยวชาญ [เพิ่มเติม] ดัชนีที่มีเงื่อนไข WHERE หรือรวมฟิลด์เพิ่มเติมในดัชนี
หากเงื่อนไขตัวกรองเป็น "คงที่" สำหรับวัตถุประสงค์ของคุณ นั่นก็คือ ไม่ได้หมายความถึงการขยายตัว รายการค่าในอนาคต - ควรใช้ดัชนี WHERE ดีกว่า สถานะบูลีน/แจงนับต่างๆ เหมาะสมกับหมวดหมู่นี้
หากสภาพการกรอง สามารถสื่อความหมายได้หลากหลายดังนั้นจึงเป็นการดีกว่าที่จะขยายดัชนีด้วยฟิลด์เหล่านี้ - เช่นเดียวกับในสถานการณ์ที่มีบิตแมปและด้านบน
ตัวอย่าง:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
การแก้ไข:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
อย่างที่คุณเห็น การกรองหายไปจากแผนโดยสิ้นเชิง และคำขอก็กลายเป็น เร็วขึ้น 5 เท่า.
#5: โต๊ะกระจัดกระจาย
เมื่อไร
ความพยายามหลายครั้งในการสร้างคิวการประมวลผลงานของคุณเอง เมื่อมีการอัพเดต/การลบเรคคอร์ดจำนวนมากในตารางนำไปสู่สถานการณ์ที่เรคคอร์ด "เสีย" จำนวนมาก
วิธีการระบุ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
แนะนำ
ดำเนินการด้วยตนเองอย่างสม่ำเสมอ สุญญากาศ [เต็ม] หรือได้รับการฝึกอบรมบ่อยครั้งพอสมควร
ในกรณีส่วนใหญ่ ปัญหาดังกล่าวมีสาเหตุมาจากองค์ประกอบคิวรีที่ไม่ดีเมื่อโทรจากตรรกะทางธุรกิจเช่นเดียวกับที่กล่าวถึง
PostgreSQL Antipatterns: ต่อสู้กับฝูง "คนตาย" .แต่คุณต้องเข้าใจว่าแม้แต่ VACUUM FULL ก็อาจไม่ช่วยเสมอไป ในกรณีเช่นนี้ คุณควรทำความคุ้นเคยกับอัลกอริทึมจากบทความ
DBA: เมื่อ VACUUM ล้มเหลว เราจะทำความสะอาดโต๊ะด้วยตนเอง .
#6: การอ่านจาก “ตรงกลาง” ของดัชนี
เมื่อไร
ดูเหมือนว่าเราอ่านได้เพียงเล็กน้อย และทุกอย่างได้รับการจัดทำดัชนีแล้ว และเราไม่ได้กรองใครที่เกินออกไป แต่เรายังคงอ่านหน้าต่างๆ มากกว่าที่เราต้องการอย่างมาก
วิธีการระบุ
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
แนะนำ
ลองดูโครงสร้างของดัชนีที่ใช้และฟิลด์คีย์ที่ระบุในแบบสอบถามอย่างใกล้ชิด ซึ่งน่าจะเป็นไปได้มากที่สุด ไม่ได้ตั้งค่าดัชนีบางส่วน. เป็นไปได้มากที่คุณจะต้องสร้างดัชนีที่คล้ายกัน แต่ไม่มีช่องคำนำหน้าหรือ
ตัวอย่าง:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
ดูเหมือนทุกอย่างจะเรียบร้อยดีแม้จะดูตามดัชนีก็ตาม แต่ก็น่าสงสัยเช่นกัน - สำหรับแต่ละบันทึกจาก 20 บันทึกที่อ่าน เราต้องลบข้อมูล 4 หน้า จำนวน 32KB ต่อบันทึก - ไม่ใช่ตัวหนาใช่ไหม และชื่อดัชนี tbl_fk_org_fk_cli_idx
กระตุ้นความคิด
การแก้ไข:
CREATE INDEX ON tbl(fk_cli);
กะทันหัน - เร็วขึ้น 10 เท่า และอ่านน้อยลง 4 เท่า!
ตัวอย่างอื่น ๆ ของสถานการณ์การใช้ดัชนีอย่างไม่มีประสิทธิภาพสามารถดูได้ในบทความ
DBA: ค้นหาดัชนีที่ไร้ประโยชน์ .
#7: ซีทีอี × ซีทีอี
เมื่อไร
ตามคำขอ ได้คะแนน CTE “อ้วน” จากโต๊ะต่างๆ แล้วจึงตัดสินใจทำระหว่างกัน JOIN
.
กรณีนี้เกี่ยวข้องกับเวอร์ชันที่ต่ำกว่า v12 หรือคำขอด้วย WITH MATERIALIZED
.
วิธีการระบุ
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
แนะนำ
วิเคราะห์คำขออย่างรอบคอบ - และ
#8: สลับไปยังดิสก์ (เขียนชั่วคราว)
เมื่อไร
การประมวลผลครั้งเดียว (การเรียงลำดับหรือการระบุเฉพาะ) ของบันทึกจำนวนมากไม่พอดีกับหน่วยความจำที่จัดสรรไว้สำหรับสิ่งนี้
วิธีการระบุ
-> *
&& temp written > 0
แนะนำ
หากจำนวนหน่วยความจำที่ใช้โดยการดำเนินการไม่เกินค่าที่ระบุของพารามิเตอร์อย่างมาก SET [LOCAL]
สำหรับคำขอ/ธุรกรรมเฉพาะ
ตัวอย่าง:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
การแก้ไข:
SET work_mem = '128MB'; -- перед выполнением запроса
ด้วยเหตุผลที่ชัดเจน หากใช้เพียงหน่วยความจำเท่านั้น ไม่ใช่ดิสก์ การสืบค้นจะดำเนินการเร็วขึ้นมาก ในขณะเดียวกัน โหลดบางส่วนจาก HDD ก็จะถูกลบออกด้วย
แต่คุณต้องเข้าใจว่าคุณไม่สามารถจัดสรรหน่วยความจำจำนวนมากได้เสมอไป เพราะหน่วยความจำนั้นไม่เพียงพอสำหรับทุกคน
#9: สถิติที่ไม่เกี่ยวข้อง
เมื่อไร
พวกเขาหลั่งไหลเข้าสู่ฐานข้อมูลจำนวนมากในคราวเดียว แต่ไม่มีเวลาที่จะขับไล่มันออกไป ANALYZE
.
วิธีการระบุ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
แนะนำ
ดำเนินการออก ANALYZE
.
สถานการณ์นี้อธิบายรายละเอียดเพิ่มเติมใน
PostgreSQL Antipatterns: สถิติคือทุกสิ่ง .
#10: “มีบางอย่างผิดพลาด”
เมื่อไร
มีการรอการล็อคที่กำหนดโดยคำขอที่แข่งขันกัน หรือมีทรัพยากรฮาร์ดแวร์ CPU/ไฮเปอร์ไวเซอร์ไม่เพียงพอ
วิธีการระบุ
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
แนะนำ
ใช้ภายนอก ระบบการตรวจสอบ เซิร์ฟเวอร์สำหรับการบล็อกหรือการใช้ทรัพยากรที่ผิดปกติ เราได้พูดคุยเกี่ยวกับเวอร์ชันของเราในการจัดการกระบวนการนี้สำหรับเซิร์ฟเวอร์หลายร้อยเครื่องแล้ว
ที่มา: will.com