von Markus Winand.

PostgreSQL Skripte für „Sortieren und Gruppieren“


Dieser Abschnitt enthält die PostgreSQL Skripte und Ausführungspläne für Kapitel 6, „Sortieren und Gruppieren.

Indexed Order By

  DROP INDEX sales_date;
CREATE INDEX sales_dt_pr ON sales (sale_date, product_id);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  ORDER BY sale_date, product_id;

Die Ausführung erfolgt ohne Sort:

                         QUERY PLAN
-----------------------------------------------------------
Index Scan using sales_dt_pr (cost=0.01..680.86 rows=376)
  Index Cond: (sale_date = (now() - '1 day'::interval day))

Derselbe Ausführungsplan wird verwendet, wenn nur nach PRODUCT_ID sortiert wird:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  ORDER BY product_id;

Wenn man den größer-gleich Operator verwendet, muss eine Sort Operation durchgeführt werden:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY product_id;

Die rows Schätzung ändert sich jedoch auf eine Zeile, und damit ist auch der Cost-Wert kleiner.

                         QUERY PLAN
--------------------------------------------------------------
Sort  (cost=8.50..8.50 rows=1 width=32)
 Sort Key: product_id
 -> Index Scan using sales_dt_pr (cost=0.00..8.49 rows=1)
    Index Cond: (sale_date >= (now() - '1 day'::interval day))

Order By ASC/DESC und NULLS FIRST/LAST

Der Index kann auch rückwärts gelesen werden:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY sale_date DESC, product_id DESC;
                           QUERY PLAN
-------------------------------------------------------------
 Index Scan Backward using sales_dt_pr (cost=0.00..8.49)
   Index Cond: (sale_date >= (now() - '1 day'::interval day))

Gemischte ASC/DESC Angeben erzwingen jedoch eine explizite Sortierung:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY sale_date ASC, product_id DESC;
                           QUERY PLAN
---------------------------------------------------------------
Sort  (cost=8.50..8.50 rows=1 width=32)
  Sort Key: sale_date, quantity
  -> Index Scan using sales_dt_pr on sales  (cost=0.00..8.49)
     Index Cond: (sale_date >= (now() - '1 day'::interval day))

PostgreSQL kann jedoch auch ASC/DESC indizieren, sodass die Abfrage wieder als pipelined order by durchgeführt werden kann.

  DROP INDEX sales_dt_pr;

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY sale_date ASC, product_id DESC;
                        QUERY PLAN
------------------------------------------------------------
Index Scan using sales_dt_pr on sales  (cost=0.00..8.49)
  Index Cond: (sale_date >= (now() - '1 day'::interval day))

PostgreSQL sortiert standardmäßig mit NULLS LAST. Die Kombination mit DESC führt jedoch dazu, dass die NULL Einträge am Anfang landen:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY sale_date ASC, product_id DESC NULLS LAST;
                         QUERY PLAN
---------------------------------------------------------------
Sort  (cost=8.50..8.50 rows=1 width=32)
  Sort Key: sale_date, product_id
  -> Index Scan using sales_dt_pr on sales  (cost=0.00..8.49)
     Index Cond: (sale_date >= (now() - '1 day'::interval day))

PostgreSQL kann jedoch auch NULLS FIRST/NULLS LAST Spezifikationen bei der Indizierung berücksichtigen, sodass auch dafür ein pipelined order by bewirkt werden kann:

  DROP INDEX sales_dt_pr;

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC NULLS LAST);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= now() - INTERVAL '1' DAY
  ORDER BY sale_date ASC, product_id DESC NULLS LAST;
                          QUERY PLAN
-------------------------------------------------------------
 Index Scan using sales_dt_pr on sales  (cost=0.00..8.49)
   Index Cond: (sale_date >= (now() - '1 day'::interval day))

Indexing Group By

Es scheint als hätte die PostgreSQL (9.0.1) Datenbank ein kleines Problem mit pipelined order by, wenn der Index eine NULLS LAST Sortierung benutzt, wie er oben angelegt wurde:

EXPLAIN
 SELECT product_id, SUM(eur_value)
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  GROUP BY product_id;
                           QUERY PLAN
---------------------------------------------------------------
HashAggregate  (cost=682.74..687.44 rows=376 width=20)
  -> Index Scan using sales_dt_pr on sales  (cost=0.00..680.86)
     Index Cond: (sale_date = (now() - '1 days'::interval day))

Ohne NULLS LAST, wird ein pipelined order by Ausgeführt:

  DROP INDEX sales_dt_pr;

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC);

EXPLAIN
 SELECT product_id, SUM(eur_value)
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  GROUP BY product_id;
                           QUERY PLAN
--------------------------------------------------------------
GroupAggregate  (cost=0.00..687.44 rows=376 width=20)
  -> Index Scan Backward using sales_dt_pr on sales
        (cost=0.00..680.86 rows=376 width=20)
     Index Cond: (sale_date = (now() - '1 day'::interval day))

Auffällig ist jedoch, dass der Index rückwärts gelesen wird. Das legt die Vermutung nahe, dass PostgreSQL intern eine ORDER BY PRODUCT_ID durchführt. Dann könnte man mit einen gegenläufigen, expliziten order by das pipelined order by unterbinden:

EXPLAIN
 SELECT product_id, SUM(eur_value)
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  GROUP BY product_id
  ORDER BY product_id DESC;
                        QUERY PLAN
---------------------------------------------------------------
GroupAggregate  (cost=0.00..687.44 rows=376 width=20)
  -> Index Scan using sales_dt_pr on sales  (cost=0.00..680.86)
     Index Cond: (sale_date = (now() - '1 day'::interval day))

Da das Ergebnis dieser These entgegenspricht, führe ich noch einen Test mit einem NULLS LAST Index durch:

  DROP INDEX sales_dt_pr;

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC NULLS LAST);

EXPLAIN
 SELECT product_id, SUM(eur_value)
   FROM sales
  WHERE sale_date = now() - INTERVAL '1' DAY
  GROUP BY product_id
  ORDER BY product_id DESC NULLS LAST;
                        QUERY PLAN
---------------------------------------------------------------
GroupAggregate  (cost=0.00..687.44 rows=376 width=20)
  -> Index Scan using sales_dt_pr on sales  (cost=0.00..680.86)
     Index Cond: (sale_date = (now() - '1 day'::interval day))

Letztendlich scheint es also so, als würde PostgreSQL eine implizite order by-Klausel einfügen, dabei aber die NULLS LAST Spezifiktion des Indexes ignorieren. Wenn man jedoch eine explizite order by-Klausel angibt, die der Indexdefinition entspricht, kann PostgreSQL ein pipelined group by durchführen.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten.

Von Markus kaufen
(Taschenbuch und PDF)

Bei Amazon kaufen
(nur Taschenbuch)

Hol dir Markus

…für ein Training in dein Büro.

Das beliebte SQL-Performance Training für Entwickler lässt dich die Datenbank besser verstehen. Erfahre mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz