Bir necha oy oldin - ommaviy PostgreSQL-ga.
O'shandan beri siz undan 6000 dan ortiq marta foydalangansiz, ammo foydali xususiyatlardan biri e'tibordan chetda qolishi mumkin. strukturaviy belgilar, bu shunday ko'rinadi:
Ularni tinglang va sizning so'rovlaringiz "ipakdek silliq bo'ladi". 🙂
Ammo jiddiy ravishda, so'rovni resurslar nuqtai nazaridan sekin va "ochko'z" qiladigan ko'plab vaziyatlar, tipik bo'lib, rejaning tuzilishi va ma'lumotlari bilan tan olinishi mumkin.
Bunday holda, har bir ishlab chiquvchi faqat o'z tajribasiga tayangan holda optimallashtirish variantini izlashi shart emas - biz unga bu erda nima sodir bo'layotganini, nima sabab bo'lishi mumkinligini aytishimiz mumkin va qanday yechim topish mumkin. Biz nima qildik.
Keling, ushbu holatlarni batafsil ko'rib chiqaylik - ular qanday ta'riflanadi va ular qanday tavsiyalarga olib keladi.
Mavzuni yaxshiroq o'rganish uchun siz avval tegishli blokni tinglashingiz mumkin , va shundan keyingina har bir misolning batafsil tahliliga o'ting:
№1: indeks "pastki saralash"
Qachon paydo bo'ladi
"Kolokolchik" MChJ mijozi uchun oxirgi hisob-fakturani ko'rsating.
Qanday aniqlash mumkin
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
tavsiyalar
Ishlatilgan indeks tartiblash maydonlari bilan kengaytiring.
Misol:
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;
Siz darhol ko'rishingiz mumkinki, 100 dan ortiq yozuvlar indeks bo'yicha olib tashlangan, keyin hammasi tartiblangan, keyin esa faqat bittasi qolgan.
Biz tuzatamiz:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Hatto bunday ibtidoiy namunada ham - 8.5x tezroq va 33x kamroq o'qish. Effekt aniqroq bo'ladi, har bir qiymat uchun ko'proq "faktlar" mavjud. fk.
Shuni ta'kidlaymanki, bunday indeks "prefiks" indeksi sifatida boshqa so'rovlar uchun avvalgisidan yomonroq bo'lmaydi. fk, qayerda saralash pk bo'lmagan va yo'q (bu haqda ko'proq o'qishingiz mumkin ). Xususan, u normal holatni ta'minlaydi aniq xorijiy kalit yordami bu maydon tomonidan.
№2: indeks kesishmasi (BimapAnd)
Qachon paydo bo'ladi
"Lyutik" NJSC nomidan tuzilgan "Kolokolchik" MChJ mijozi uchun barcha shartnomalarni ko'rsating.
Qanday aniqlash mumkin
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scantavsiyalar
yaratish kompozit indeks ikkala manbadan maydonlar bo'yicha yoki ikkinchisidan mavjud maydonlardan birini kengaytiring.
Misol:
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); -- отбор по конкретной паре
Biz tuzatamiz:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Bu erda daromad kichikroq, chunki Bitmap Heap Scan o'z-o'zidan samarali. Lekin baribir 7x tezroq va 2.5x kamroq o'qish.
№3: Indekslarni birlashtirish (BitmapOr)
Qachon paydo bo'ladi
Qayta ishlash uchun birinchi 20 ta eng qadimgi "o'z" yoki tayinlanmagan so'rovlarni ko'rsating.
Qanday aniqlash mumkin
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scantavsiyalar
Foydalanish UNION [hamma] shart OR bloklarining har biri uchun pastki so'rovlarni birlashtirish.
Misol:
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;
Biz tuzatamiz:
(
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, больше и не надо
Biz birinchi blokda barcha 20 ta kerakli yozuvlar darhol olinganligidan foydalandik, shuning uchun ikkinchisi, "qimmatroq" Bitmap uyasi skanerlashi ham bajarilmadi - natijada 22 marta tezroq, 44 marta kamroq o'qish!
Ushbu optimallashtirish usuli haqida batafsilroq hikoya aniq misollar bo'yicha maqolalarida o‘qilishi mumkin и .
Umumiy versiya bir nechta tugmalar bilan tartiblangan tanlash (va faqat const/NULL juftligi uchun emas) maqolada muhokama qilinadi .
# 4: Biz juda ko'p o'qiymiz
Qachon paydo bo'ladi
Qoida tariqasida, mavjud so'rovga "boshqa filtrni biriktirmoqchi" bo'lganingizda paydo bo'ladi.
"Va sizda ham xuddi shunday yo'q, lekin marvarid tugmalari bilan? " "Olmos qo'l" filmi
Masalan, yuqoridagi vazifani o'zgartirib, maqsadlaridan qat'i nazar, qayta ishlash uchun dastlabki 20 ta eng qadimgi "tanqidiy" so'rovlarni ko'rsating.
Qanday aniqlash mumkin
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
tavsiyalar
Ixtisoslashgan [ko'proq] yarating WHERE bandi bilan indeks yoki indeksga qo'shimcha maydonlarni qo'shing.
Filtrlash sharti sizning vazifalaringiz uchun "statik" bo'lsa - ya'ni kengaytirishni o'z ichiga olmaydi Kelajakdagi qiymatlar ro'yxati - WHERE indeksidan foydalanish yaxshiroqdir. Har xil mantiqiy/enum holatlari ushbu toifaga yaxshi mos keladi.
Agar filtrlash holati bo'lsa turli qiymatlarni qabul qilishi mumkin, indeksni ushbu maydonlar bilan kengaytirish yaxshiroqdir - BitmapVa yuqoridagi vaziyatda bo'lgani kabi.
Misol:
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;
Biz tuzatamiz:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Ko'rib turganingizdek, rejadan filtrlash butunlay yo'qoldi va so'rov bo'ldi 5 marta tezroq.
№5: siyrak stol
Qachon paydo bo'ladi
Jadvaldagi ko'plab yangilanishlar / yozuvlarni o'chirish juda ko'p "o'lik" yozuvlar holatiga olib kelganda, o'zingizning vazifangizni qayta ishlash navbatini yaratishga bo'lgan turli urinishlar.
Qanday aniqlash mumkin
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tavsiyalar
Muntazam ravishda qo'lda bajaring VAKUUM [TOʻLIQ] yoki etarli darajada tez-tez ishlov berishga erishing uning parametrlarini, jumladan, nozik sozlash orqali .
Aksariyat hollarda bunday muammolar biznes mantig'idan chaqirilganda so'rovlarning noto'g'ri tuzilishidan kelib chiqadi, masalan, maqolada muhokama qilinganlar. .
Ammo biz tushunishimiz kerakki, hatto VACUUM FULL ham har doim ham yordam bera olmaydi. Bunday holatlar uchun siz maqoladagi algoritm bilan tanishishingiz kerak. .
№6: indeksning "o'rtasi" dan o'qish
Qachon paydo bo'ladi
Aftidan, ular bir oz o'qidilar va hamma narsa indeksatsiya qilindi va ular hech kimni ortiqcha filtrlamadi - ammo shunga qaramay, biz xohlaganimizdan ko'ra ko'proq sahifalar o'qildi.
Qanday aniqlash mumkin
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tavsiyalar
Amaldagi indeks tuzilishini va so'rovda ko'rsatilgan asosiy maydonlarni diqqat bilan ko'rib chiqing - ehtimol, indeks qismi o'rnatilmagan. Siz shunga o'xshash indeksni yaratishingiz kerak bo'ladi, lekin prefiks maydonlarisiz yoki .
Misol:
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;
Har bir narsa, hatto indeks nuqtai nazaridan ham yaxshi ko'rinadi, lekin qandaydir shubhali - o'qilgan 20 ta yozuvning har biri uchun 4 sahifa ma'lumotni olib tashlash kerak edi, har bir yozuv uchun 32 KB - bu qalin emasmi? Ha va indeks nomi tbl_fk_org_fk_cli_idx fikrlashga olib keladi.
Biz tuzatamiz:
CREATE INDEX ON tbl(fk_cli);
Birdan - 10 marta tezroq va o'qish uchun 4 marta kamroq!
Indekslardan samarasiz foydalanishning ko'proq misollari uchun maqolaga qarang .
№7: CTE × CTE
Qachon paydo bo'ladi
Talabda "semiz" CTE ball oldi turli jadvallardan, va keyin ular orasida qilishga qaror qildi JOIN.
Bu holat v12 dan past versiyalar yoki soʻrovlar uchun tegishli WITH MATERIALIZED.
Qanday aniqlash mumkin
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
tavsiyalar
So'rovni diqqat bilan tahlil qiling ? Ha bo'lsa, unda hstore/json da "lug'at" ni qo'llang da tasvirlangan modelga muvofiq .
№8: diskka almashtirish (vaqt yozilgan)
Qachon paydo bo'ladi
Ko'p sonli yozuvlarni bir martalik qayta ishlash (saralash yoki noyoblashtirish) buning uchun ajratilgan xotiraga mos kelmaydi.
Qanday aniqlash mumkin
-> *
&& temp written > 0tavsiyalar
Agar operatsiya tomonidan ishlatiladigan xotira miqdori parametrning belgilangan qiymatidan katta bo'lmasa , uni tuzatish kerak. Siz darhol hamma uchun konfiguratsiyaga kirishingiz mumkin yoki o'ta olasiz SET [LOCAL] muayyan so'rov/tranzaksiya uchun.
Misol:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Biz tuzatamiz:
SET work_mem = '128MB'; -- перед выполнением запроса
Aniq sabablarga ko'ra, agar disk emas, balki faqat xotira ishlatilsa, so'rov juda tez bo'ladi. Shu bilan birga, yukning bir qismi HDD dan ham chiqariladi.
Ammo shuni tushunishingiz kerakki, ko'p xotirani ajratish har doim ham ishlamaydi - bu hamma uchun etarli bo'lmaydi.
№9: Ahamiyatsiz statistika
Qachon paydo bo'ladi
Bazaga birdaniga ko'p quyildi, lekin uni haydab chiqarishga ulgurmadilar ANALYZE.
Qanday aniqlash mumkin
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10tavsiyalar
Xuddi shunday sarflang ANALYZE.
Ushbu holat maqolada batafsil tavsiflangan .
№10: "nimadir noto'g'ri ketdi"
Qachon paydo bo'ladi
Raqobatchi so‘rovda qulf kutilmoqda yoki protsessor/gipervisor apparat resurslari yetarli emas edi.
Qanday aniqlash mumkin
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
tavsiyalar
Tashqi vositadan foydalaning monitoring tizimi resursni bloklash yoki g'ayritabiiy iste'mol qilish uchun server. Biz allaqachon yuzlab serverlar uchun ushbu jarayonni tashkil qilish versiyamiz haqida gapirgan edik. и .
Manba: www.habr.com