Anda telah menggunakannya lebih dari 6000 kali sejak saat itu, namun salah satu fitur berguna yang mungkin luput dari perhatian adalah petunjuk struktural, yang terlihat seperti ini:
Dengarkan mereka dan permintaan Anda akan "menjadi selembut sutra". π
Tapi serius, banyak situasi yang membuat permintaan lambat dan "rakus" dalam hal sumber daya, tipikal dan dapat dikenali dari struktur dan data rencana.
Dalam hal ini, setiap pengembang individu tidak perlu mencari opsi pengoptimalan sendiri, hanya mengandalkan pengalamannya sendiri - kami dapat memberi tahu dia apa yang terjadi di sini, apa alasannya, dan bagaimana menemukan solusi. Itulah yang kami lakukan.
Mari kita lihat lebih dekat kasus-kasus ini - bagaimana mereka didefinisikan dan rekomendasi apa yang mereka berikan.
Untuk perendaman yang lebih baik dalam topik, pertama-tama Anda dapat mendengarkan blok yang sesuai dari laporan saya di PGConf.Russia 2020, dan baru kemudian pergi ke analisis terperinci dari setiap contoh:
Anda dapat segera melihat bahwa lebih dari 100 catatan dikurangi dengan indeks, yang kemudian diurutkan semuanya, dan kemudian hanya satu yang tersisa.
Kami memperbaiki:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- Π΄ΠΎΠ±Π°Π²ΠΈΠ»ΠΈ ΠΊΠ»ΡΡ ΡΠΎΡΡΠΈΡΠΎΠ²ΠΊΠΈ
Bahkan pada sampel primitif seperti itu - 8.5x lebih cepat dan pembacaan 33x lebih sedikit. Efeknya akan semakin jelas, semakin banyak "fakta" yang Anda miliki untuk setiap nilai. fk.
Saya perhatikan bahwa indeks seperti itu akan berfungsi sebagai indeks "awalan" tidak lebih buruk dari yang sebelumnya untuk kueri lain fk, di mana menyortir pk tidak dan tidak (Anda dapat membaca lebih lanjut tentang ini dalam artikel saya tentang menemukan indeks yang tidak efisien). Secara khusus, itu akan memberikan normal dukungan kunci asing eksplisit oleh bidang ini.
#2: persimpangan indeks (BitmapAnd)
Kapan?
Tampilkan semua kontrak untuk klien "LLC Kolokolchik" menyimpulkan atas nama "NJSC Lyutik".
Bagaimana mengidentifikasi
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan
Rekomendasi
membuat indeks komposit menurut bidang dari kedua sumber atau perluas salah satu bidang yang ada dari yang kedua.
Contoh:
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); -- ΠΎΡΠ±ΠΎΡ ΠΏΠΎ ΠΊΠΎΠ½ΠΊΡΠ΅ΡΠ½ΠΎΠΉ ΠΏΠ°ΡΠ΅
Di sini keuntungannya lebih kecil, karena Bitmap Heap Scan sendiri cukup efektif. Tapi bagaimanapun juga 7x lebih cepat dan pembacaan 2.5x lebih sedikit.
#3: Menggabungkan Indeks (BitmapOr)
Kapan?
Tampilkan 20 permintaan terlama "milik sendiri" atau permintaan yang belum ditetapkan untuk diproses, dengan prioritas milik sendiri.
Bagaimana mengidentifikasi
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan
Rekomendasi
Menggunakan UNI [SEMUA] untuk menggabungkan subkueri untuk setiap kondisi ATAU blok.
Contoh:
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, Π±ΠΎΠ»ΡΡΠ΅ ΠΈ Π½Π΅ Π½Π°Π΄ΠΎ
Kami mengambil keuntungan dari fakta bahwa semua 20 record yang diperlukan segera diterima di blok pertama, jadi blok kedua, dengan Bitmap Heap Scan yang lebih "mahal", bahkan tidak dieksekusi - akibatnya 22x lebih cepat, pembacaan 44x lebih sedikit!
Buat [lebih] terspesialisasi indeks dengan klausa WHERE atau sertakan bidang tambahan dalam indeks.
Jika kondisi pemfilteran "statis" untuk tugas Anda - yaitu tidak termasuk ekspansi daftar nilai di masa mendatang - lebih baik menggunakan indeks WHERE. Berbagai status boolean/enum cocok dengan kategori ini.
Jika kondisi filtrasi dapat mengambil nilai yang berbeda, lebih baik memperluas indeks dengan bidang-bidang ini - seperti dalam situasi dengan BitmapAnd di atas.
Contoh:
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;
Seperti yang Anda lihat, pemfilteran dari paket benar-benar hilang, dan permintaan telah menjadi 5 kali lebih cepat.
# 5: tabel jarang
Kapan?
Berbagai upaya untuk membuat antrian pemrosesan tugas Anda sendiri, ketika sejumlah besar pembaruan / penghapusan catatan di atas meja menyebabkan situasi sejumlah besar catatan "mati".
Lakukan secara manual secara teratur VAKUM [PENUH] atau mencapai pemrosesan yang cukup sering vakum otomatis dengan menyempurnakan parameternya, termasuk untuk meja tertentu.
Tampaknya mereka membaca sedikit, dan semuanya diindeks, dan mereka tidak memfilter siapa pun secara berlebihan - tetapi tetap saja, halaman yang dibaca jauh lebih banyak daripada yang kami inginkan.
Perhatikan baik-baik struktur indeks yang digunakan dan bidang kunci yang ditentukan dalam kueri - kemungkinan besar, bagian indeks tidak disetel. Kemungkinan besar Anda perlu membuat indeks serupa, tetapi tanpa bidang awalan, atau belajar untuk mengulangi nilai-nilai mereka.
Segalanya tampak baik-baik saja, bahkan dalam hal indeks, tetapi entah bagaimana mencurigakan - untuk masing-masing dari 20 catatan yang dibaca, 4 halaman data harus dikurangi, 32KB per catatan - bukankah itu tebal? Ya dan nama indeks tbl_fk_org_fk_cli_idx mengarah pada pemikiran.
Pemrosesan satu kali (pengurutan atau keunikan) dari sejumlah besar rekaman tidak sesuai dengan memori yang dialokasikan untuk ini.
Bagaimana mengidentifikasi
-> *
&& temp written > 0
Rekomendasi
Jika jumlah memori yang digunakan oleh operasi tidak melebihi nilai parameter yang ditetapkan pekerjaan_mem, itu harus diperbaiki. Anda dapat langsung di konfigurasi untuk semua orang, atau Anda dapat melalui SET [LOCAL] untuk permintaan/transaksi tertentu.
Contoh:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Untuk alasan yang jelas, jika hanya memori yang digunakan dan bukan disk, kueri akan jauh lebih cepat. Pada saat yang sama, sebagian beban juga dikeluarkan dari HDD.
Tetapi Anda perlu memahami bahwa mengalokasikan banyak memori juga tidak akan selalu berhasil - itu tidak akan cukup untuk semua orang.
#9: Statistik yang tidak relevan
Kapan?
Banyak yang dituangkan ke pangkalan sekaligus, tetapi mereka tidak punya waktu untuk mengusirnya ANALYZE.
Bagaimana mengidentifikasi
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
Ada kunci yang menunggu permintaan bersaing, atau sumber daya perangkat keras CPU/hypervisor tidak cukup.
Bagaimana mengidentifikasi
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- ΡΠΈΡΠ°Π»ΠΈ ΠΌΠ°Π»ΠΎ, Π½ΠΎ ΡΠ»ΠΈΡΠΊΠΎΠΌ Π΄ΠΎΠ»Π³ΠΎ
Rekomendasi
Gunakan eksternal sistem pemantauan server untuk pemblokiran atau konsumsi sumber daya yang tidak normal. Kami telah berbicara tentang versi kami dalam mengatur proses ini untuk ratusan server. di sini ΠΈ di sini.