Mengapa pertanyaan SELECT DISTINCT saya lambat pada jadual Postgres dengan kunci utama komposit, dan bagaimana saya boleh meningkatkan prestasinya?

Mengoptimumkan Pertanyaan DISTINCT PILIH Lambat dalam Postgres

Artikel ini menangani isu prestasi yang dihadapi semasa melaksanakan SELECT DISTINCT pertanyaan pada jadual Postgres yang besar dengan kunci utama komposit. Senario khusus yang melibatkan jadual dengan hampir dua juta baris dan kunci utama komposit (product_id, trade_id) diperiksa. Walaupun pertanyaan SELECT DISTINCT product_id sepatutnya pantas kerana indeks kunci utama, prestasi perlahan yang tidak dijangka diperhatikan.

Analisis Punca Punca:

Pilihan perancang pertanyaan untuk imbasan berurutan, dan bukannya menggunakan indeks, telah dikenal pasti sebagai hambatan. Ini dikaitkan dengan pengedaran data jadual: hanya 40 ID produk unik wujud, yang membawa kepada pengulangan nilai indeks yang tinggi. Ini menghasilkan banyak kuar indeks dan akses berjujukan yang tidak cekap.

Penyelesaian Berkesan: CTE Rekursif

Untuk memintas pengehadan ini dan memanfaatkan pengindeksan dengan cekap, ungkapan jadual biasa rekursif (CTE) dicadangkan sebagai alternatif unggul kepada SELECT DISTINCT:

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses required
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;</code>

CTE rekursif ini secara berkesan meniru imbasan langkau indeks. Ia secara berulang mendapatkan nilai product_id yang berbeza dalam tertib diisih, dengan itu mengelakkan penalti prestasi yang dikaitkan dengan imbasan berjujukan yang tidak cekap. Penggunaan indeks pada lajur product_id adalah penting untuk prestasi optimum dengan pendekatan ini.

Nota Penting: Semasa ciri imbasan langkau indeks Postgres sedang dibangunkan, penyelesaian berasaskan CTE ini menawarkan penyelesaian yang mantap dan cekap untuk senario yang diterangkan, meningkatkan prestasi pertanyaan dengan ketara.

Atas ialah kandungan terperinci Mengapa pertanyaan SELECT DISTINCT saya lambat pada jadual Postgres dengan kunci utama komposit, dan bagaimana saya boleh meningkatkan prestasinya?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!