Geekly articles weekly

PostgreSQL Antipatterns: tekan kamus di JOIN berat

Kami melanjutkan serangkaian artikel yang ditujukan untuk mempelajari cara-cara yang kurang dikenal untuk meningkatkan kinerja pertanyaan PostgreSQL "tampaknya sederhana":


Jangan berpikir bahwa saya tidak terlalu suka BERGABUNG ... :)

Tetapi seringkali tanpa itu, permintaan secara signifikan lebih produktif daripada bersamanya. Oleh karena itu, hari ini kami akan berusaha sepenuhnya untuk menyapu habis sumber daya yang intensif - dengan bantuan kamus.



Dimulai dengan PostgreSQL 12, beberapa situasi yang dijelaskan di bawah ini dapat dimainkan sedikit berbeda karena non-materialisasi CTE secara default . Perilaku ini dapat dikembalikan ke dengan menggunakan kunci MATERIALIZED .

Banyak "fakta" pada kosakata terbatas


Mari kita ambil aplikasi yang sangat nyata - Anda perlu mendaftar pesan masuk atau tugas aktif dengan pengirim:

 25.01 |  .. |    . 22.01 |  .. |    :   JOIN. 20.01 |  .. |   . 18.01 |  .. |    : JOIN    . 16.01 |  .. |   .

Dalam dunia abstrak, penulis tugas harus didistribusikan secara merata di antara semua karyawan organisasi kami, tetapi dalam kenyataannya, tugas datang, sebagai suatu peraturan, dari jumlah orang yang cukup terbatas - "dari atasan" ke atas hierarki atau "dari sekutu" dari departemen tetangga (analis, desainer pemasaran ...).

Mari kita asumsikan bahwa dalam organisasi kami yang terdiri dari 1000 orang, hanya 20 penulis (biasanya bahkan lebih sedikit) yang menetapkan tugas untuk setiap artis tertentu dan menggunakan pengetahuan subjek ini untuk mempercepat permintaan "tradisional".

Generator skrip
 --  CREATE TABLE person AS SELECT id , repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name" , '2000-01-01'::date - (random() * 1e4)::integer birth_date FROM generate_series(1, 1000) id; ALTER TABLE person ADD PRIMARY KEY(id); --     CREATE TABLE task AS WITH aid AS ( SELECT id , array_agg((random() * 999)::integer + 1) aids FROM generate_series(1, 1000) id , generate_series(1, 20) GROUP BY 1 ) SELECT * FROM ( SELECT id , '2020-01-01'::date - (random() * 1e3)::integer task_date , (random() * 999)::integer + 1 owner_id FROM generate_series(1, 100000) id ) T , LATERAL( SELECT aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id FROM aid WHERE id = T.owner_id LIMIT 1 ) a; ALTER TABLE task ADD PRIMARY KEY(id); CREATE INDEX ON task(owner_id, task_date); CREATE INDEX ON task(author_id);

Kami menampilkan 100 tugas terakhir untuk artis tertentu:

 SELECT task.* , person.name FROM task LEFT JOIN person ON person.id = task.author_id WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100;


[lihat menjelaskan.tensor.ru]

Ternyata 1/3 dari seluruh waktu dan 3/4 pembacaan halaman data dibuat hanya untuk mencari penulis 100 kali - untuk setiap tugas yang ditampilkan. Tetapi kita tahu bahwa di antara ratusan ini hanya ada 20 yang berbeda - dapatkah pengetahuan ini digunakan?

kamus hstore


Kami menggunakan tipe hstore untuk menghasilkan "kamus" nilai kunci:

 CREATE EXTENSION hstore

Cukup bagi kami untuk memasukkan ID penulis dan namanya di kamus, sehingga nanti kami bisa mengekstrak menggunakan kunci ini:

 --    WITH T AS ( SELECT * FROM task WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100 ) --      , dict AS ( SELECT hstore( -- hstore(keys::text[], values::text[]) array_agg(id)::text[] , array_agg(name)::text[] ) FROM person WHERE id = ANY(ARRAY( SELECT DISTINCT author_id FROM T )) ) --     SELECT * , (TABLE dict) -> author_id::text -- hstore -> key FROM T;


[lihat menjelaskan.tensor.ru]

Butuh 2 kali lebih sedikit waktu untuk mendapatkan informasi tentang orang dan 7 kali lebih sedikit data dibaca ! Selain "penipuan", hasil ini membantu kami untuk mencapai ekstraksi massal catatan dari tabel dalam satu pass menggunakan = ANY(ARRAY(...)) .

Entri tabel: serialisasi dan deserialisasi


Tetapi bagaimana jika kita perlu menyimpan dalam kamus bukan satu bidang teks, tetapi seluruh catatan? Dalam hal ini, kemampuan PostgreSQL untuk bekerja dengan menulis tabel sebagai nilai tunggal akan membantu kami:

 ... , dict AS ( SELECT hstore( array_agg(id)::text[] , array_agg(p)::text[] --  #1 ) FROM person p WHERE ... ) SELECT * , (((TABLE dict) -> author_id::text)::person).* --  #2 FROM T;

Mari kita lihat apa yang terjadi di sini:

  1. Kami mengambil p sebagai alias untuk catatan lengkap dari tabel orang dan mengumpulkan array dari mereka.
  2. Larik entri ini disusun kembali menjadi larik string teks (orang [] :: teks []) untuk memasukkannya ke dalam kamus hstore sebagai larik nilai.
  3. Setelah menerima catatan tertaut, kami mengeluarkannya dari kamus dengan kunci sebagai string teks.
  4. Kita perlu mengubah teks menjadi nilai dari tipe tabel orang (untuk setiap tabel jenis nama yang sama secara otomatis dibuat).
  5. "Menyebarkan" catatan yang diketik ke dalam kolom menggunakan (...).* .

kamus json


Tetapi trik seperti itu, seperti yang telah kami terapkan di atas, tidak akan berfungsi jika tidak ada tipe tabel yang sesuai untuk membuat "unfastening". Situasi yang sama persis akan muncul, dan jika sebagai sumber data untuk serialisasi kami mencoba menggunakan baris CTE, dan bukan tabel "nyata" .

Dalam hal ini, fungsi untuk bekerja dengan json akan membantu kami:

 ... , p AS ( --   CTE SELECT * FROM person WHERE ... ) , dict AS ( SELECT json_object( --    json array_agg(id)::text[] , array_agg(row_to_json(p))::text[] --   json    ) FROM p ) SELECT * FROM T , LATERAL( SELECT * FROM json_to_record( ((TABLE dict) ->> author_id::text)::json --     json ) AS j(name text, birth_date date) --     ) j;

Perlu dicatat bahwa ketika menggambarkan struktur target, kita tidak bisa mendaftar semua bidang string sumber, tetapi hanya yang benar-benar kita butuhkan. Jika kita memiliki tabel "asli", maka lebih baik menggunakan fungsi json_populate_record .

Kami masih memiliki akses ke kamus sekali, tetapi biaya serialisasi json [cukup] cukup tinggi , jadi masuk akal untuk menggunakan metode ini hanya dalam beberapa kasus ketika CTE Scan "jujur" menunjukkan dirinya lebih buruk.

Menguji kinerja


Jadi, kami punya dua cara untuk membuat serialisasi data ke dalam kamus - hstore / json_object . Selain itu, array kunci dan nilai juga dapat dihasilkan dalam dua cara, dengan konversi internal atau eksternal ke teks: array_agg (i :: text) / array_agg (i) :: text [] .

Mari kita periksa keefektifan berbagai jenis serialisasi menggunakan contoh sintetis murni - kita membuat serialisasi sejumlah kunci yang berbeda :

 WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, ...) i ) TABLE dict;

Skrip evaluasi: serialisasi
 WITH T AS ( SELECT * , ( SELECT regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (\d+\.\d+) ms$', '\1')::real et FROM ( SELECT array_agg(el) ea FROM dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$ explain analyze WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, $$ || (1 << v) || $$) i ) TABLE dict $$) T(el text) ) T ) et FROM generate_series(0, 19) v , LATERAL generate_series(1, 7) i ORDER BY 1, 2 ) SELECT v , avg(et)::numeric(32,3) FROM T GROUP BY 1 ORDER BY 1;



Pada PostgreSQL 11, hingga sekitar ukuran kamus 2 ^ 12 kunci, serialisasi dalam json membutuhkan waktu lebih sedikit . Kombinasi json_object dan konversi tipe "internal" dari array_agg(i::text) adalah yang paling efisien.

Sekarang mari kita coba membaca nilai dari setiap tombol 8 kali - karena jika Anda tidak mengakses kamus, lalu mengapa itu diperlukan?

Skrip evaluasi: membaca dari kamus
 WITH T AS ( SELECT * , ( SELECT regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (\d+\.\d+) ms$', '\1')::real et FROM ( SELECT array_agg(el) ea FROM dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$ explain analyze WITH dict AS ( SELECT json_object( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, $$ || (1 << v) || $$) i ) SELECT (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text FROM generate_series(1, $$ || (1 << (v + 3)) || $$) i $$) T(el text) ) T ) et FROM generate_series(0, 19) v , LATERAL generate_series(1, 7) i ORDER BY 1, 2 ) SELECT v , avg(et)::numeric(32,3) FROM T GROUP BY 1 ORDER BY 1;



Dan ... sudah sekitar 2 ^ 6 kunci, membaca dari kamus json mulai kehilangan untuk membaca dari hstore beberapa kali, untuk jsonb hal yang sama terjadi pada 2 ^ 9.
Kesimpulan akhir:

  • jika Anda perlu BERGABUNG dengan catatan berulang kali - lebih baik menggunakan "tabel matching"
  • jika kamus Anda diharapkan kecil dan Anda akan membaca sedikit darinya - Anda dapat menggunakan json [b]
  • dalam semua kasus lain, hstore + array_agg (i :: text) akan lebih efisien

Source: https://habr.com/ru/post/id485398/

More articles:


All Articles