Sering mengeluh bahwa hitungan (*) di PostgreSQL sangat lambat.Pada artikel ini, saya ingin menjelajahi opsi sehingga Anda mendapatkan hasilnya secepat mungkin.Mengapa hitung (*) begitu lambat?
Kebanyakan orang mengerti tanpa masalah bahwa permintaan berikut ini akan dieksekusi dengan lambat:SELECT count(*)
FROM ;
Bagaimanapun, ini adalah kueri yang kompleks, dan PostgreSQL harus menghitung hasilnya sebelum ia tahu berapa banyak baris yang akan dikandungnya.Tetapi banyak orang terkejut ketika mereka mengetahui bahwa permintaan berikut ini lambat:SELECT count(*) FROM large_table;
Namun, jika Anda berpikir lagi, semua hal di atas berlaku: PostgreSQL harus menghitung hasil yang ditetapkan sebelum dapat menghitungnya. Karena "penghitung baris ajaib" tidak disimpan dalam tabel (seperti pada MyISAM MySQL), satu-satunya cara untuk menghitung baris adalah dengan melihatnya.Oleh karena itu, count (*) biasanya melakukan pemindaian tabel sekuensial, yang bisa sangat mahal.Apakah "*" dalam hitungan (*) masalah?
"*" Dalam SELECT * FROM ... berlaku untuk semua kolom. Oleh karena itu, banyak orang menemukan bahwa menggunakan count (*) tidak efisien, dan sebagai gantinya menggunakan count (id) atau count (1) sebagai gantinya.Tetapi "*" dalam hitungan (*) sama sekali berbeda, itu hanya berarti "string" dan tidak berkembang sama sekali (pada kenyataannya, itu adalah "agregat dengan argumen nol"). Penghitungan notasi (1) atau penghitungan (id) sebenarnya lebih lambat daripada penghitungan (*), karena harus diperiksa apakah argumennya NULL atau tidak (hitungan, seperti kebanyakan agregat, mengabaikan argumen NULL).Jadi Anda tidak akan mencapai apa pun dengan menghindari "*".Menggunakan hanya pemindaian indeks
Sangat menggoda untuk memindai indeks kecil, bukan seluruh tabel, untuk menghitung jumlah baris. Namun, ini tidak begitu sederhana di PostgreSQL karena strategi manajemen konkurensi multi-versi. Setiap versi baris ("tuple") berisi informasi tentang snapshot basis data mana yang terlihat . Tetapi informasi (redundan) ini tidak disimpan dalam indeks. Oleh karena itu, biasanya tidak cukup untuk menghitung entri dalam indeks, karena PostgreSQL harus melihat entri tabel ("heap tuple") untuk memastikan bahwa entri indeks terlihat.Untuk mengurangi masalah ini, PostgreSQL telah menerapkan peta visibilitas , struktur data yang menyimpan informasi tentang apakah semua tupel dalam blok tabel dapat dilihat oleh semua orang atau tidak.Jika sebagian besar blok dalam tabel terlihat sepenuhnya, maka pemindaian indeks tidak perlu sering mengunjungi sekelompok tupel untuk menentukan visibilitas. Pemindaian indeks semacam itu disebut "pemindaian indeks saja," dan seringkali lebih cepat untuk memindai indeks untuk menghitung baris.Sekarang VACUUM yang mendukung peta visibilitas, jadi pastikan autovacuum cukup sering dilakukan jika Anda ingin menggunakan indeks kecil untuk mempercepat hitungan (*).Menggunakan tabel pivot
Saya menulis di atas bahwa PostgreSQL tidak menyimpan jumlah baris dalam sebuah tabel.Mempertahankan jumlah baris seperti itu merupakan biaya overhead yang besar, karena peristiwa ini terjadi pada setiap modifikasi data dan tidak membuahkan hasil. Itu akan menjadi transaksi yang buruk. Selain itu, karena permintaan yang berbeda dapat melihat versi string yang berbeda, penghitung juga harus diversi.Tetapi tidak ada yang menghalangi Anda untuk menerapkan penghitung garis seperti itu sendiri.Misalkan Anda ingin melacak jumlah baris dalam mytable. Anda dapat melakukannya sebagai berikut:START TRANSACTION;
CREATE TABLE mytable_count(c bigint);
CREATE FUNCTION mytable_count() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE mytable_count SET c = c + 1;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE mytable_count SET c = c - 1;
RETURN OLD;
ELSE
UPDATE mytable_count SET c = 0;
RETURN NULL;
END IF;
END;$$;
CREATE CONSTRAINT TRIGGER mytable_count_mod
AFTER INSERT OR DELETE ON mytable
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE mytable_count();
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
-- initialize the counter table
INSERT INTO mytable_count
SELECT count(*) FROM mytable;
COMMIT;
Kami melakukan segalanya dalam satu transaksi sehingga tidak ada perubahan data pada transaksi bersamaan dapat "hilang" karena kondisi dering.Ini dijamin oleh perintah CREATE TRIGGER yang mengunci tabel dalam mode SHARE ROW EXCLUSIVE, yang mencegah semua perubahan bersamaan.Kelemahannya adalah bahwa semua modifikasi data paralel harus menunggu sampai penghitungan SELECT (*) dijalankan.Ini memberi kita alternatif yang sangat cepat untuk menghitung (*), tetapi dengan biaya memperlambat semua perubahan data dalam tabel. Menggunakan pemicu kendala yang ditangguhkan memastikan bahwa kunci baris di mytable_count sesingkat mungkin untuk meningkatkan concurrency.Meskipun tabel counter ini dapat menerima banyak pembaruan, tidak ada bahayaTidak ada "kembung tabel" , karena semua ini akan menjadi pembaruan "panas" (pembaruan HOT).Anda benar-benar perlu menghitung (*)
Terkadang solusi terbaik adalah mencari alternatif.Seringkali perkiraannya cukup baik dan Anda tidak perlu jumlah yang tepat. Dalam hal ini, Anda dapat menggunakan skor yang digunakan PostgreSQL untuk menjadwalkan pertanyaan:SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';
Nilai ini diperbarui oleh autovacuum dan autoanalisis, sehingga tidak boleh melebihi 10%. Anda dapat mengurangi autovacuum_analyze_scale_factor untuk tabel ini sehingga analisis otomatis berjalan di sana lebih sering.Memperkirakan jumlah hasil kueri
Sejauh ini, kami telah mengeksplorasi cara mempercepat penghitungan baris tabel.Tetapi kadang-kadang Anda perlu tahu berapa banyak baris pernyataan SELECT akan kembali tanpa benar-benar mengeksekusi kueri.Jelas, satu-satunya cara untuk mendapatkan jawaban yang akurat untuk pertanyaan ini adalah dengan melengkapi permintaan. Tetapi jika nilainya cukup baik, Anda dapat menggunakan pengoptimal PostgreSQL untuk mendapatkannya.Fungsi sederhana berikut menggunakan SQL dinamis dan EXPLAIN untuk mendapatkan rencana eksekusi permintaan yang dilewati sebagai argumen, dan mengembalikan perkiraan jumlah baris:CREATE FUNCTION row_estimator(query text) RETURNS bigint
LANGUAGE plpgsql AS
$$DECLARE
plan jsonb;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;
Jangan gunakan fungsi ini untuk memproses pernyataan SQL yang tidak dipercaya, karena secara inheren rentan terhadap injeksi SQL.