PostgreSQL Skripte für „Sortieren und Gruppieren“


Diese Seite für

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

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

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.