Warum ist meine SELECT DISTINCT-Abfrage in einer Postgres-Tabelle mit einem zusammengesetzten Primärschlüssel langsam und wie kann ich ihre Leistung verbessern?

Optimierung langsamer SELECT DISTINCT-Abfragen in Postgres

Dieser Artikel befasst sich mit Leistungsproblemen, die beim Ausführen von SELECT DISTINCT Abfragen für eine große Postgres-Tabelle mit einem zusammengesetzten Primärschlüssel auftreten. Untersucht wird ein spezifisches Szenario mit einer Tabelle mit fast zwei Millionen Zeilen und einem zusammengesetzten Primärschlüssel (product_id, trade_id). Während eine SELECT DISTINCT product_id-Abfrage aufgrund des Primärschlüsselindex idealerweise schnell sein sollte, wurde eine unerwartet langsame Leistung beobachtet.

Ursachenanalyse:

Als Engpass wurde die Entscheidung des Abfrageplaners für einen sequentiellen Scan anstelle der Verwendung des Index identifiziert. Dies ist auf die Datenverteilung der Tabelle zurückzuführen: Es sind nur 40 eindeutige Produkt-IDs vorhanden, was zu einem hohen Grad an Indexwertwiederholungen führt. Dies führt zu zahlreichen Indexprüfungen und einem ineffizienten sequentiellen Zugriff.

Effektive Lösung: Rekursiver CTE

Um diese Einschränkung zu umgehen und die Indizierung effizient zu nutzen, wird ein rekursiver gemeinsamer Tabellenausdruck (CTE) als überlegene Alternative zu SELECT DISTINCT:

vorgeschlagen

<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>

Dieser rekursive CTE ahmt effektiv einen Index-Skip-Scan nach. Es ruft iterativ unterschiedliche product_id-Werte in sortierter Reihenfolge ab und vermeidet so die Leistungseinbußen, die mit dem ineffizienten sequentiellen Scan verbunden sind. Die Verwendung eines Index für die Spalte product_id ist für die optimale Leistung dieses Ansatzes von entscheidender Bedeutung.

Wichtiger Hinweis:Während sich die Index-Skip-Scan-Funktion von Postgres in der Entwicklung befindet, bietet diese CTE-basierte Problemumgehung eine robuste und effiziente Lösung für das beschriebene Szenario und verbessert die Abfrageleistung erheblich.

Das obige ist der detaillierte Inhalt vonWarum ist meine SELECT DISTINCT-Abfrage in einer Postgres-Tabelle mit einem zusammengesetzten Primärschlüssel langsam und wie kann ich ihre Leistung verbessern?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!