Warum ist SELECT DISTINCT bei einer Tabelle mit einem zusammengesetzten Primärschlüssel in PostgreSQL langsam und wie kann es optimiert werden?

Bei Tabellen, die zusammengesetzte Primärschlüssel in PostgreSQL verwenden SELECT DISTINCTGründe für langsame Abfragegeschwindigkeit und Optimierungsmethoden

In einer PostgreSQL-Datenbank SELECT DISTINCT hängt die Ausführungsgeschwindigkeit einer Abfrage von der Tabellenstruktur und der Datenverteilung ab. Obwohl die tickers-Spalte in der product_id-Tabelle Teil eines zusammengesetzten Primärschlüssels ist und daher darauf indiziert ist, führt eine Abfrage, die SELECT DISTINCT product_id FROM tickers verwendet, um eindeutiges product_id zu erhalten, standardmäßig einen sequentiellen Scan durch.

Gründe für langsame Leistung

Der Hauptgrund für die langsame Leistung von

ist, dass die Tabelle doppelte Werte von product_id enthält. Das bedeutet, dass PostgreSQL für jedes abgerufene eindeutige product_id die gesamte Tabelle scannen muss, um sicherzustellen, dass keine Duplikate vorhanden sind.

Lösung: Index-Skip-Scan simulieren

Da PostgreSQL Index-Skip-Scans noch nicht nativ unterstützt, können Sie rekursive CTEs (Common Table Expressions) verwenden, um dieses Verhalten zu simulieren. Dieser CTE ruft iterativ Duplikate ab und verwirft sie, wodurch effektiv alle bis auf eine Instanz jedes eindeutigen product_id herausgefiltert werden.

Verbesserte Lösung

WITH RECURSIVE cte AS (
   (   -- 括号必需
   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  -- 横向引用
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;

Diese Abfrage verwendet einen horizontalen Join, um die sortierte Tabelle zu durchlaufen und mithilfe von orderBy eindeutige product_id-Werte abzurufen.

Fazit

Die Ausführungszeit von SELECT DISTINCT product_id-Abfragen kann erheblich verbessert werden, indem ein Index-Skip-Scan mithilfe der CTE-Methode simuliert wird, wodurch die Zeit reduziert wird, die zum Abrufen eindeutiger tickerss aus der product_id-Tabelle erforderlich ist.

Das obige ist der detaillierte Inhalt vonWarum ist SELECT DISTINCT bei einer Tabelle mit einem zusammengesetzten Primärschlüssel in PostgreSQL langsam und wie kann es optimiert werden?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!