Die Auswahl der richtigen index für PostgreSQL query

Vereinfachte Tabelle:

CREATE TABLE products (
product_no integer PRIMARY KEY,
sales integer,
status varchar(16),
category varchar(16));

CREATE INDEX index_products_sales ON products (sales);
CREATE INDEX index_products_status ON products (status);
CREATE INDEX index_products_category ON products (category);

PostgreSQL version 8.4. Die Spalten 'status' und 'Kategorie'

Sind es 20 Millionen Produkte/Reihen, die sich auf 15 Kategorien.

Einer der am häufigsten verwendeten Abfragen wird, bekommen die drei am meisten verkauften Produkten, ausgenommen Produkte in den Kategorien 'cat3' und 'cat7':

SELECT product_no, sales 
FROM products 
WHERE status = 'something' AND category NOT IN ('cat3', 'cat7') 
ORDER BY sales DESC 
LIMIT 3;

Limit  (cost=0.00..8833.39 rows=3 width=12) (actual time=9235.332..9356.284 rows=3 loops=1)
   ->  Index Scan using index_products_sales on products  (cost=0.00..68935806.85 rows=23412 width=12) (actual time=9235.327..9356.278 rows=3 loops=1)
     Filter: (((category)::text <> ALL ('{cat3,cat7}'::text[])) AND ((status)::text = 'something'::text))

Was wäre der beste index für die Herstellung dieser speziellen Abfrage schneller laufen?

CREATE INDEX index_special AUF Produkte (status, sales DESC NULLS LAST), WO NICHT (Kategorie = 'cat3' and Kategorie = 'cat7');
Die WHERE Zustand der index ist logisch falsch, alle Zeilen zu qualifizieren, weil diese Art keine Zeile passen beide Kategorien gleichzeitig. Ich fügte hinzu, eine Antwort.
Du hast Recht, natürlich.

InformationsquelleAutor Backend Viking | 2012-06-21

13

Erstellen eine teilweise mehrspaltigen index mit dieser besonderen Art um:
```
CREATE INDEX products_status_sales_partial_idx ON products (status, sales DESC)
WHERE  category NOT IN ('cat3','cat7');
```
Ändern Sie Ihre Abfrage leicht:
```
SELECT product_no, sales 
FROM   products 
WHERE  status = 'something'
AND    category NOT IN ('cat3', 'cat7') 
ORDER  BY status, sales DESC 
LIMIT  3;
```
Hinzufügen status als erstes element der ORDER BY Klausel scheint überflüssig und sinnlos. Aber versuchen Sie es.

Warum?

Den query planner ist nicht schlau genug, um zu verstehen, dass mit
```
WHERE  status = 'something' ...
ORDER  BY sales DESC
```
die Sortierreihenfolge des index (status, sales DESC) entspricht, als logische Konsequenz. So ist es zu Lesen alle qualifizierenden Zeilen, Sortieren und wählen Sie die top 3.

Durch hinzufügen status zu den ORDER BY aktivieren Sie die Abfrage-Planer zu Lesen, die ersten 3 Einträge aus dem index direkt. Erwarten Sie ein speed-up von mehrere Größenordnungen.

Getestet mit PostgreSQL 8.4 und 9.1.

InformationsquelleAutor Erwin Brandstetter
2

Ich denke, ein b-tree-index ist immer noch Ihre beste Wette. Ich könnte falsch sein, aber. Ich denke, ich würde testen zwei Dinge.

Zunächst einen partiellen index auf die Kategorie, schließt die 'cat3' und 'cat7'.
```
CREATE INDEX index_products_category ON products (category)
  WHERE category NOT IN ('cat3','cat7');
```
Zweite, eine absteigende Sortierung auf die Umsätze aus.
```
CREATE INDEX index_products_sales ON products (sales DESC);
```
Entweder einer von diesen könnte sich verlangsamen andere Abfragen, obwohl, so müssen Sie möglicherweise eine oder beide dieser zusätzlich zu den bestehenden Indizes.
- Danke für die Antwort, aber sollte es wirklich zu sagen "(Kategorie = 'cat3' und Kategorie = 'cat7')" in der WHERE-NICHT-Klausel?
- Nein, es sollte nicht, und ich bin nicht sicher, warum ich schrieb so etwas wie, dass die WHERE-Klausel in den ersten Platz. Sollte ich schon wach gewesen um diese Zeit. WHERE category NOT IN ('cat3','cat7') ist, wie ich normalerweise denke. Hier, auf einem Tisch von einer million Zeilen, dieser läuft bis zur Fertigstellung in etwa .06ms.
InformationsquelleAutor Mike Sherrill 'Cat Recall'

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

Warum?