Bagaimanakah Saya Boleh Menyimpan dan Mengakses Sub-pilihan Yang Sering Digunakan dengan Cekap dalam PostgreSQL?

Mengoptimumkan Subquery Kerap dalam PostgreSQL

Pengguna PostgreSQL kerap menghadapi pertanyaan bergantung pada subkueri berulang, selalunya mengagregatkan data daripada jadual berkaitan. Artikel ini meneroka kaedah yang cekap untuk mengurus corak subkueri biasa ini, meningkatkan prestasi dan kebolehbacaan kod. Senario biasa melibatkan pengambilan data agregat daripada jadual kedua berdasarkan hubungan kunci asing.

Pendekatan Pandangan: Penyelesaian Teguh

Walaupun membenamkan subkueri secara langsung sebagai lajur pseudo tidak mungkin, fungsi VIEW PostgreSQL menyediakan alternatif yang sangat baik. A VIEW mentakrifkan jadual maya berdasarkan jadual dan pertanyaan sedia ada, dengan berkesan mencipta paparan tersuai data. Ini mengelakkan pengubahsuaian struktur jadual asas sambil mencapai hasil yang diingini.

Lajur Dijana (PostgreSQL 11 ): Prestasi Dipertingkat

PostgreSQL 11 dan versi yang lebih baru menawarkan lajur yang dijana (juga dipanggil lajur maya). Ini membenarkan penentuan lajur yang nilainya dikira menggunakan ungkapan, mencerminkan matlamat untuk membenamkan subkueri. Pendekatan ini selalunya menghasilkan prestasi yang lebih baik daripada tontonan.

Meniru Lajur Dijana dalam Versi Lama

Untuk versi sebelum PostgreSQL 11, atau apabila pendekatan lajur maya diutamakan, fungsi yang ditentukan pengguna boleh mensimulasikan lajur yang dijana. Fungsi ini mengambil jenis baris jadual sebagai input dan mengira nilai yang dikehendaki. Fungsi itu kemudiannya dipautkan kepada lajur pseudo, mencapai fungsi yang serupa dengan lajur yang dijana.

Pelaksanaan Praktikal

Mari kita ilustrasikan dengan contoh:

<code class="language-sql">-- Create tables
CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);

-- Sample data
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
INSERT INTO tbl_b VALUES
(1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);

-- Function to emulate the generated column
CREATE FUNCTION col3(tbl_a)
RETURNS int8
LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = .a_id
$func$;</code>

Sekarang, akses col3 lajur pseudo:

<code class="language-sql">SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

SELECT *, a.col3 FROM tbl_a a;</code>

Kelebihan Lajur Dijana

Berbanding dengan paparan, tawaran lajur yang dijana:

• Penyepaduan lancar: Disertakan dalam SELECT * pertanyaan tanpa sebutan yang jelas.
• Kemas kini dinamik: Secara automatik mencerminkan perubahan dalam jadual asas.
• Prestasi yang dioptimumkan: Memanfaatkan pengoptimuman pertanyaan PostgreSQL untuk kecekapan.

Kesimpulan

Walaupun pembenaman langsung subkueri tidak disokong, PostgreSQL menyediakan alternatif yang berkesan – paparan dan lajur yang dijana – untuk mengurus subkueri yang kerap digunakan. Kaedah ini meningkatkan prestasi pertanyaan dan kejelasan kod, yang membawa kepada pengurusan pangkalan data yang lebih cekap. Pilih pendekatan yang paling sesuai dengan versi PostgreSQL anda dan keperluan prestasi.

Atas ialah kandungan terperinci Bagaimanakah Saya Boleh Menyimpan dan Mengakses Sub-pilihan Yang Sering Digunakan dengan Cekap dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!