Resep untuk Kueri SQL yang Sakit

Beberapa bulan yang lalu kami mengumumkan jelaskan.tensor.ru - publik layanan untuk mem-parsing dan memvisualisasikan rencana kueri ke PostgreSQL.

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:

#1: indeks "undersorting"

Kapan?

Tunjukkan faktur terakhir untuk klien "LLC Kolokolchik".

Bagaimana mengidentifikasi

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Rekomendasi

Indeks digunakan perluas dengan bidang sortir.

Contoh:

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;

[lihat penjelasan.tensor.ru]

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); -- добавили ключ сортировки

[lihat penjelasan.tensor.ru]

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); -- отбор по конкретной паре

[lihat penjelasan.tensor.ru]

Kami memperbaiki:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[lihat penjelasan.tensor.ru]

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;

[lihat penjelasan.tensor.ru]

Kami memperbaiki:

(
  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, больше и не надо

[lihat penjelasan.tensor.ru]

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!

Cerita yang lebih rinci tentang metode pengoptimalan ini pada contoh-contoh konkret bisa dibaca di artikel PostgreSQL Antipatterns: GABUNG dan OR yang Berbahaya и PostgreSQL Antipatterns: Kisah Penyempurnaan Iteratif Pencarian berdasarkan Nama, atau "Mengoptimalkan Bolak-balik".

Versi umum pemilihan yang dipesan oleh beberapa tombol (dan bukan hanya untuk sepasang const / NULL) dibahas dalam artikel tersebut SQL HowTo: tulis while-loop langsung di kueri, atau "Elementary three-way".

#4: Kami terlalu banyak membaca

Kapan?

Biasanya, ini terjadi saat Anda ingin "melampirkan filter lain" ke permintaan yang ada.

“Dan kamu tidak memiliki hal yang sama, tapi dengan kancing mutiara? " film "Tangan Berlian"

Misalnya, memodifikasi tugas di atas, menampilkan 20 permintaan "kritis" terlama pertama untuk diproses, apa pun tujuannya.

Bagaimana mengidentifikasi

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Rekomendasi

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;

[lihat penjelasan.tensor.ru]

Kami memperbaiki:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[lihat penjelasan.tensor.ru]

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".

Bagaimana mengidentifikasi

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Rekomendasi

Lakukan secara manual secara teratur VAKUM [PENUH] atau mencapai pemrosesan yang cukup sering vakum otomatis dengan menyempurnakan parameternya, termasuk untuk meja tertentu.

Dalam banyak kasus, masalah tersebut disebabkan oleh tata letak kueri yang buruk saat dipanggil dari logika bisnis, seperti yang dibahas di PostgreSQL Antipatterns: melawan gerombolan "mati".

Tetapi kita harus memahami bahwa VACUUM FULL pun tidak selalu dapat membantu. Untuk kasus seperti itu, Anda harus membiasakan diri dengan algoritme dari artikel tersebut. DBA: saat VACUUM lewat, kami membersihkan tabel secara manual.

#6: membaca dari "tengah" indeks

Kapan?

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.

Bagaimana mengidentifikasi

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Rekomendasi

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.

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, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[lihat penjelasan.tensor.ru]

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.

Kami memperbaiki:

CREATE INDEX ON tbl(fk_cli);

[lihat penjelasan.tensor.ru]

Tiba-tiba - 10 kali lebih cepat dan 4 kali lebih sedikit untuk membaca!

Untuk lebih banyak contoh penggunaan indeks yang tidak efisien, lihat artikel DBA: temukan indeks yang tidak berguna.

#7: KTE × KTE

Kapan?

dalam permintaan mencetak CTE "gemuk". dari tabel yang berbeda, dan kemudian memutuskan untuk melakukannya di antara mereka JOIN.

Kasing ini relevan untuk versi di bawah v12 atau permintaan dengan WITH MATERIALIZED.

Bagaimana mengidentifikasi

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Rekomendasi

Analisis permintaan dengan hati-hati apakah CTE dibutuhkan di sini sama sekali? Jika ya, maka terapkan "kamus" di hstore/json sesuai dengan model yang dijelaskan di PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: tukar ke disk (ditulis sementara)

Kapan?

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;

[lihat penjelasan.tensor.ru]

Kami memperbaiki:

SET work_mem = '128MB'; -- перед выполнением запроса

[lihat penjelasan.tensor.ru]

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

Rekomendasi

Habiskan sama ANALYZE.

Situasi ini dijelaskan secara lebih rinci di PostgreSQL Antipatterns: statistik adalah kepala dari segalanya.

#10: "ada yang tidak beres"

Kapan?

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.

Sumber: www.habr.com