Warum ist meine PostgreSQL SELECT DISTINCT-Abfrage mit einem zusammengesetzten Primärschlüssel so langsam?

PostgreSQL SELECT DISTINCT Leistungsprobleme mit zusammengesetzten Schlüsseln

Die Verwendung von SELECT DISTINCT für eine PostgreSQL-Tabelle mit einem zusammengesetzten Primärschlüssel (z. B. (product_id, trade_id)) kann überraschend langsam sein. Der Abfrageplaner wählt häufig einen sequentiellen Scan, anstatt den Index effizient zu nutzen.

Warum ist es langsam?

• Sequentieller Scan-Vorzug: Der Planer bevorzugt möglicherweise einen vollständigen Tabellenscan, auch mit einem Index, was zu deutlich längeren Abfragezeiten führt.
• Fehlender Index-Skip-Scan: PostgreSQL unterstützt nativ keine Index-Skip-Scans, eine Optimierung, die eindeutige Werte effizient abruft, indem Duplikate innerhalb des Indexes übersprungen werden.

Die Lösung: Index-Skip-Scan mit einem CTE simulieren

Obwohl ein echter Index-Skip-Scan nicht verfügbar ist, können wir sein Verhalten mithilfe eines rekursiven Common Table Expression (CTE) effektiv nachahmen:

WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   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
   )
SELECT * FROM cte;

Dieser CTE durchläuft eindeutige product_id Werte in sortierter Reihenfolge und nutzt den Index für (product_id) für Effizienz.

Vorteile dieses Ansatzes

• Geschwindigkeitsverbesserung: Diese Methode reduziert die Ausführungszeit der Abfrage drastisch. Tests zeigen eine Reduzierung auf 0,75 Millisekunden bei einer Tabelle mit 2,25 Millionen Zeilen.
• Indexnutzung: Es nutzt effektiv sowohl den zusammengesetzten Primärschlüsselindex (product_id, trade_id) als auch den Index für (product_id).
• Agnostisch zur Datenverteilung: Die Leistung bleibt unabhängig von der Datenverteilung innerhalb der Tabelle konstant.

Das obige ist der detaillierte Inhalt vonWarum ist meine PostgreSQL SELECT DISTINCT-Abfrage mit einem zusammengesetzten Primärschlüssel so langsam?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!