Anda telah menggunakannya lebih 6000 kali sejak itu, tetapi salah satu ciri berguna yang mungkin tidak disedari ialah petunjuk struktur, yang kelihatan seperti ini:
Dengarkan mereka dan permintaan anda akan "menjadi selembut sutera". π
Tetapi serius, banyak situasi yang membuat permintaan perlahan dan "rakus" dari segi sumber, adalah tipikal dan boleh dikenali oleh struktur dan data pelan.
Dalam kes ini, setiap pembangun individu tidak perlu mencari pilihan pengoptimuman sendiri, bergantung semata-mata pada pengalamannya sendiri - kita boleh memberitahu dia apa yang berlaku di sini, apakah sebabnya, dan bagaimana untuk mendapatkan penyelesaian. Itulah yang kami lakukan.
Mari kita lihat dengan lebih dekat kes ini - bagaimana ia ditakrifkan dan apakah pengesyoran yang dibawa olehnya.
Untuk rendaman yang lebih baik dalam topik, anda boleh mula-mula mendengar blok yang sepadan daripada laporan saya di PGConf.Russia 2020, dan hanya kemudian pergi ke analisis terperinci bagi setiap contoh:
Anda boleh melihat dengan serta-merta bahawa lebih daripada 100 rekod telah ditolak oleh indeks, yang kemudiannya semua diisih, dan kemudian satu-satunya yang tinggal.
Kami membetulkan:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- Π΄ΠΎΠ±Π°Π²ΠΈΠ»ΠΈ ΠΊΠ»ΡΡ ΡΠΎΡΡΠΈΡΠΎΠ²ΠΊΠΈ
Walaupun pada sampel primitif sedemikian - 8.5x lebih pantas dan 33x lebih sedikit bacaan. Kesannya akan menjadi lebih jelas, lebih banyak "fakta" yang anda ada untuk setiap nilai. fk.
Saya perhatikan bahawa indeks sedemikian akan berfungsi sebagai indeks "awalan" tidak lebih buruk daripada yang sebelumnya untuk pertanyaan lain dengan fk, di mana menyusun mengikut pk bukan dan bukan (anda boleh membaca lebih lanjut tentang ini dalam artikel saya tentang mencari indeks yang tidak cekap). Khususnya, ia akan memberikan normal sokongan utama asing yang jelas oleh bidang ini.
#2: persimpangan indeks (BitmapAnd)
Apabila timbul
Tunjukkan semua kontrak untuk pelanggan "LLC Kolokolchik" yang dibuat bagi pihak "NJSC Lyutik".
Bagaimana untuk mengenal pasti
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan
Cadangan
mewujudkan indeks komposit mengikut medan daripada kedua-dua sumber atau mengembangkan salah satu medan sedia ada daripada medan 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 keuntungan adalah lebih kecil, kerana Bitmap Heap Scan agak berkesan dengan sendirinya. Tetapi bagaimanapun 7x lebih pantas dan 2.5x lebih sedikit bacaan.
#3: Menggabungkan Indeks (BitmapOr)
Apabila timbul
Tunjukkan 20 permintaan "sendiri" tertua pertama atau belum ditetapkan untuk pemprosesan, dengan keutamaan sendiri.
Bagaimana untuk mengenal pasti
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan
Cadangan
Guna KESATUAN [SEMUA] untuk menggabungkan subkueri bagi setiap blok syarat ATAU.
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 kesempatan daripada fakta bahawa semua 20 rekod yang diperlukan telah diperoleh dengan segera di blok pertama, jadi yang kedua, dengan Imbasan Timbunan Bitmap yang lebih "mahal", tidak dilaksanakan - akibatnya 22x lebih pantas, 44x lebih sedikit bacaan!
Buat [lebih] khusus indeks dengan klausa WHERE atau masukkan medan tambahan dalam indeks.
Jika keadaan penapisan adalah "statik" untuk tugasan anda - iaitu tidak termasuk pengembangan senarai nilai pada masa hadapan - lebih baik menggunakan indeks WHERE. Pelbagai status boolean/enum sesuai dengan kategori ini.
Jika keadaan penapisan boleh mengambil nilai yang berbeza, adalah lebih baik untuk mengembangkan indeks dengan medan ini - seperti dalam situasi dengan BitmapDan 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, penapisan daripada pelan telah hilang sepenuhnya, dan permintaan telah menjadi 5 kali lebih cepat.
#5: jadual jarang
Apabila timbul
Pelbagai percubaan untuk membuat giliran pemprosesan tugas anda sendiri, apabila sejumlah besar kemas kini / pemadaman rekod di atas meja membawa kepada situasi sejumlah besar rekod "mati".
Secara manual dijalankan dengan kerap VACUUM [PENUH] atau mencapai pemprosesan yang cukup kerap autovakum dengan memperhalusi parameternya, termasuk untuk jadual tertentu.
Dalam kebanyakan kes, masalah sedemikian disebabkan oleh reka letak pertanyaan yang lemah apabila dipanggil daripada logik perniagaan, seperti yang dibincangkan dalam Antipattern PostgreSQL: memerangi gerombolan "mati".
Nampaknya mereka membaca sedikit, dan semuanya telah diindeks, dan mereka tidak menapis sesiapa tambahan - tetapi tetap, lebih banyak halaman dibaca daripada yang kita mahukan.
Lihat dengan teliti struktur indeks yang digunakan dan medan utama yang dinyatakan dalam pertanyaan - kemungkinan besar, bahagian indeks tidak ditetapkan. Anda berkemungkinan besar perlu mencipta indeks yang serupa, tetapi tanpa medan awalan, atau belajar untuk mengulangi nilai mereka.
Segala-galanya nampaknya baik-baik saja, walaupun dari segi indeks, tetapi entah bagaimana mencurigakan - untuk setiap satu daripada 20 rekod yang dibaca, 4 muka surat data terpaksa ditolak, 32KB setiap rekod - bukankah ia tebal? Ya dan nama indeks tbl_fk_org_fk_cli_idx membawa kepada pemikiran.
Pemprosesan sekali sahaja (isih atau keunikan) sejumlah besar rekod tidak sesuai dengan memori yang diperuntukkan untuk ini.
Bagaimana untuk mengenal pasti
-> *
&& temp written > 0
Cadangan
Jika jumlah memori yang digunakan oleh operasi tidak banyak melebihi nilai set parameter kerja_mem, ia harus diperbetulkan. Anda boleh serta-merta dalam konfigurasi untuk semua orang, atau anda boleh melalui SET [LOCAL] untuk permintaan/urus niaga tertentu.
Contoh:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Atas sebab yang jelas, jika hanya memori digunakan, dan bukan cakera, maka pertanyaan akan dilaksanakan dengan lebih cepat. Pada masa yang sama, sebahagian daripada beban juga dikeluarkan dari HDD.
Tetapi anda perlu memahami bahawa memperuntukkan banyak memori akan sentiasa tidak berfungsi sama ada - ia tidak akan mencukupi untuk semua orang.
#9: Statistik tidak relevan
Apabila timbul
Banyak yang dituangkan ke pangkalan sekaligus, tetapi mereka tidak mempunyai masa untuk menghalaunya ANALYZE.
Bagaimana untuk mengenal pasti
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
Terdapat kunci menunggu permintaan bersaing, atau sumber perkakasan CPU/hypervisor tidak mencukupi.
Bagaimana untuk mengenal pasti
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- ΡΠΈΡΠ°Π»ΠΈ ΠΌΠ°Π»ΠΎ, Π½ΠΎ ΡΠ»ΠΈΡΠΊΠΎΠΌ Π΄ΠΎΠ»Π³ΠΎ
Cadangan
Gunakan alat luaran sistem pemantauan pelayan untuk menyekat atau penggunaan sumber yang tidak normal. Kami telah pun bercakap tentang versi kami mengatur proses ini untuk ratusan pelayan. di sini ΠΈ di sini.