Postgre Query Performance Problem

[361]

Hallöchen,

ich habe mal eine Frage an alle Datenbankspezialisten da draußen. Ich habe eine Query, welche für den Anwender viel zu lange dauert, insbesondere wenn diese Abfrage mehrmals ausgeführt wird, z.B. aufgrund von Refreshs o.ä... Die Abfrage wurde bereits mit Hilfe von Analyze optimiert, allerdings bin ich nun mit meinen Kenntnissen an einem Punkt angekommen, wo ich keine Ideen mehr habe.

Beschreibung: Die unten aufgeführte Abfrage soll es ermöglichen, zu einem Katalogteil dessen Menge im Lager je Station zu finden. Die Abfrage funktioniert inhaltlich einwandfrei, allerdings ist aufgrund der mittlerweile angefallenen Daten eine Abfragedauer von ca. 30 Sekunden zustandegekommen. Ich führe das Statement beim ersten Zugriff einmalig aus und nutze es dann die gesamte Sitzung über. Bei Daten-Refreshs, sprich wenn sich Daten diese Abfrage betreffend geändert haben, dauert die Abfrage allerdings wieder ewig, was relativ oft vorkommt. Weitere Gegebenheiten sind:

Zeilen mtx_stockparts: 100.000
Zeilen mtx_catalogparts : 250.000
Zeilen cd_stations : 50

Query:
select sp.id_catalogpart AS "id", sp.id_station AS "id1", sp.id_location AS "id2", sum(sp.quantity) AS "count"
from mtx_stockparts sp, mtx_catalogparts cp, cd_stations st
where sp._entrytype = 0
and st._deletedat is null
and sp._deletedat is null
and sp.id_station = st.id
and sp.id_catalogpart = cp.id
group by sp.id_catalogpart, sp.id_station, sp.id_location


Analyze-Auszug:
"GroupAggregate (cost=82456.84..85899.37 rows=32866 width=85) (actual time=9359.633..12518.661 rows=208288 loops=1)"
" -> Sort (cost=82456.84..83063.18 rows=242536 width=85) (actual time=9359.131..10720.273 rows=208667 loops=1)"
" Sort Key: sp.id_catalogpart, sp.id_station, sp.id_location"
" Sort Method: external merge Disk: 19960kB"
" -> Hash Join (cost=14957.97..49158.61 rows=242536 width=85) (actual time=1115.546..5276.611 rows=208667 loops=1)"
" Hash Cond: (sp.id_catalogpart = cp.id)"
" -> Hash Join (cost=3.94..20942.70 rows=242536 width=85) (actual time=0.893..1823.669 rows=208687 loops=1)"
" Hash Cond: (sp.id_station = st.id)"
" -> Seq Scan on mtx_stockparts sp (cost=0.00..17603.89 rows=242536 width=85) (actual time=0.074..655.781 rows=234393 loops=1)"
" Filter: ((_deletedat IS NULL) AND (_entrytype = 0))"
" -> Hash (cost=3.45..3.45 rows=39 width=27) (actual time=0.708..0.708 rows=39 loops=1)"
" -> Seq Scan on cd_stations st (cost=0.00..3.45 rows=39 width=27) (actual time=0.030..0.328 rows=39 loops=1)"
" Filter: (_deletedat IS NULL)"
" -> Hash (cost=10779.68..10779.68 rows=215868 width=27) (actual time=1114.393..1114.393 rows=215868 loops=1)"
" -> Seq Scan on mtx_catalogparts cp (cost=0.00..10779.68 rows=215868 width=27) (actual time=0.010..530.099 rows=215868 loops=1)"
"Total runtime: 12995.986 ms"


Wer hätte Vorschläge zur Optimierung dieser Query oder den beteiligten Tabellen? Ich bin speziell darauf aus, Query und Tabllen/DB zu optimieren weil sich die Daten tendenziell noch verzehnfachen werden (riesige Katalog/Artikeldatenbanken), es sei denn jemand hat noch eine bessere Idee. Es wurden bereits einige Tests gemacht, immer "on demand" je Katalogteil zu laden, was allerdings unterm Strich dann beim Navigieren noch länger dauert und für den Anwender kein zufriedenstellendes Ergebnis lieferte. Ich bin für jeden Tipp und Hilfe dankbar :thumb:

Viele Grüße
Seb

[himitsu]

Mit der neueren Joint-Syntax hast du es auch schonmal versucht?

SQL-Code:

SELECT sp.id_catalogpart AS "id", sp.id_station AS "id1", sp.id_location AS "id2", sum(sp.quantity) AS "count"
FROM mtx_stockparts sp
JOIN mtx_catalogparts cp ON sp.id_catalogpart = cp.id
JOIN cd_stations st ON sp.id_station = st.id
WHERE sp._entrytype = 0
  AND st._deletedat IS NULL
  AND sp._deletedat IS NULL
GROUP BY sp.id_catalogpart, sp.id_station, sp.id_location

Ob das so einen Unterschied ausmacht, weiß ich nicht.

SQL-Code:

SELECT sp.id_catalogpart AS "id", sp.id_station AS "id1", sp.id_location AS "id2", sum(sp.quantity) AS "count"
FROM mtx_stockparts sp
JOIN mtx_catalogparts cp ON sp.id_catalogpart = cp.id
JOIN cd_stations st ON st._deletedat IS NULL AND sp.id_station = st.id
WHERE sp._entrytype = 0 AND sp._deletedat IS NULL
GROUP BY sp.id_catalogpart, sp.id_station, sp.id_location

[haentschman]

Hallo...

die wichtigste Frage ist wie die Indizies der Tabellen aussehen.

[mjustin]

Code:

" Sort Method: external merge Disk: 19960kB"

Kann das "external merge Disk" die Ursache sein für die lange Dauer für Sortierung / Gruppierung? Es klingt als würde das merge nicht im RAM erfolgen :)

-> Hat die Datenbank ausreichend Puffer-RAM frei?

Es scheinen ca 200000 Ergebnissätze zu sein? Da ist es keine Frage, dass die Übertragung vom Server zum Client auch einige Zeit dauert. Um das zu prüfen, kann man ein select count(*) zum Vergleich anstatt der Felder angeben.

[jobo]

Ich würde mich Bug anschließen.
Technisch wäre die Indizierung der Filterspalte ..date.. zu prüfen. Praktisch würde es aber kaum einen Unterschied machen, wenn nur wenig Artikel Unterschied mit / ohne Filter existieren. Ergibt trotzdem einen Fullscan für das SUM.

Also Buffer für Sort/Merge erhöhen.

Vielleicht gelingt es dir auch, die Abfrage fachlich abzuspecken. Wenn Du die Anzahl der Items On Stock brauchst, kannst Du die Catalog items vielleicht rauslassen, evtl. auch die 3 Tabelle.
Geht natürlich nur, wenn der Join dieser Tabellen nicht letztlich auch eine wichtige Filterfunktion bildet. Ausgegeben werden die Daten zumindest nicht alle.

Der Optimizer sollte m.E. keinen Unterschied zwischen den Jointechniken machen, aber das lässt sich ja leicht testen.

[361]

Hallo zurück und vielen Dank Euch für Eure Antworten :) Also fange ich mal an diese zu beantworten:

@himitsu: Ich werde das gleich ausprobieren.
@haentschman: Die Indizes sehen wie folgt aus:

CREATE INDEX idx_mtx_catalogparts_deletedat
ON mtx_catalogparts
USING btree
(_deletedat);

CREATE INDEX idx_mtx_stockparts_deletedat
ON mtx_stockparts
USING btree
(_deletedat);

CREATE INDEX idx_mtx_stockparts_id_catalogpart
ON mtx_stockparts
USING btree
(id_catalogpart);

CREATE INDEX idx_cd_stations_deletedat
ON cd_stations
USING btree
(_deletedat);

Am Punkt Indizes erhoffe ich mir auch eine Performancesteigerung. Ich hatte hier auch schon mit herumprobiert allerdings ohne Erfolg.

@mjustin: Werde ich gleich ausprobieren, Server und Client laufen zusammen auf dem selben (Entwicklungs-)Rechner mit SSD. Es sind ca. 220.000 Datensätze ja. Welcher Parameter genau wäre "Puffer-RAM" in der pg-Konfiguration? Die Maschine hat 8 GB RAM

@jobo: Ich schaue gleich mal in die Konfigurationsdatei. Kannst Du mir sagen welche Parameter dort in Hinblick auf die Server-Hardware zum Test zu erhöhen wären? Die Maschine hat 8 GB RAM mit SSD (Entwicklungsrechner). Leider kann ich nichts weglassen, da ich die Spalten allesamt je nach UI-Sicht benötige. Ich brauche die Daten jweils zum (Catalog)Part.


Ich werde nun Eure Tipps mal ausprobieren und bedanke mich noch einmal recht herzlich dafür :)

[361]

Hallo,

hier sind die ersten recht interessanten Ergebnisse:

Ich habe zuerst Himitsus Abfrage versucht. Es gab einen Unterschied von ca. 500 ms.
Dann habe ich mich an die conf-Datei gemacht und den Server mehr Ressourcen gegeben. Das führte dazu, dass der Server die Abfrage nun in 4,3 Sekunden ausführt. Beeindruckend! Danach gab es allerdings keinen Unterschied zwischen beiden Abfragen mehr. Ich habe für dieses Ergebnis folgende Parameter geändert und um ca. 30 % erhöht:


# - Memory -
shared_buffers = 512 -> 768MB # min 128kB
temp_buffers = 32 -> 128MB # min 800kB
work_mem = 16 -> 32MB # min 64kB
maintenance_work_mem = 16 -> 64MB # min 1MB
max_stack_depth = 2MB # NICHT GEÄNDERT


1.) Kann mir jemand von Euch optimalere Werte bei einem System mit 8 GB RAM und mechanischer Server-Platte (hohe Umdrehungszahl + gespiegelt) sowie für eine SSD nennen? Mir fiel auf, dass das System durch die geänderten Werte nun kaum noch CPU für diese Abfrage benötigt. Vorher war die Last anhaltend 100%. Ich versuche es nachzuvollziehen, um das Verhalten eventuell auch für andere Abfragen verstehen zu können.

2.) Was kann ich nun noch mit Indizes versuchen? Besteht hier noch eine Möglichkeit etwas herauszuholen? Optimal für den Anwender wäre ein Zeitwert von ca. 1 Sekunden für eine solche Abfrage. Mir ist klar, dass das schon sehr gewagt ist, allerdings wären Hardware-Erweiterungen auch noch möglich (RAM, HDD, CPU).

3.) Was bewirken die Planner-Einstellungen bzw. wie oder was muss ich tun um den Planner einzustellen? Zeitplan? Oder macht er das automatisch. Ich werde parallel noch etwas die Suchmaschine dazu bedienen, da ich Postgre bisher fast immer nur in seiner Standard-Einstellung genutzt habe.

Viele Grüße und besten Dank
Seb

[shmia]

Folgende Felder bieten sich als Index an:

• mtx_stockparts.id_catalogpart
• mtx_stockparts.id_station
• mtx_stockparts.id_location
• mtx_catalogparts.id (ist wahrscheinlich schon Primary Key)
• cd_stations.id (ist wahrscheinlich schon Primary Key)

[Furtbichler]

Wer benötigt eigentlich die 200k Datensätze?

[361]

Die Anwender des Statistikmoduls.

[Furtbichler]

Die einzig legitime Anwendergruppe.

Wie groß ist die Datenmenge in etwa in kB, die übertragen wird? Angenommen, es sind 200 Bytes pro Datensatz, dann wären das alleine schon 200k x 0,2k = 40 MB+Overhead, die irgendwie vom Server zum Client geschaufelt werden müssen. Das dauert ja auch seine Zeit.

Ändern sich viele oder nur wenige Datensätze pro Query?

Wenn es nur relativ wenige sind, dann könnte ein Cache helfen, der nur die zwischen zwei Abfragen veränderten DS liefert. Ich hatte es einmal mit einer Auftragsliste mit ca. 40k Einträgen zu tun (ca. 5k pro Eintrag). Jeder Datensatz (Auftrag) hat einen PK. Meine Query enthielt also pro Datensatz den PK und die Nutzdaten. Diese Query hat den Aufbau

Code:

SELECT * FROM View_Auftrag WHERE <Bedingung>

Es dauerte ziemlich lange, bis die Query komplett abgearbeitet wurde.

Eine ähnliche Query, die jedoch nur die PK der Aufträge lieferte, also

Code:

SELECT * FROM View_Auftrags_ID WHERE <Bedingung>

war jedoch wesentlich schneller.

Mein Cache führte also nur die 2 Abfrage durch und erstellte eine Liste der PK, die geändert oder neu hinzugekommen war. Anschließend wurde die 'View_Auftrag' nur für diese PK aufgerufen, was auch widerum wesentlich schneller war, als ständig die 200k Datensätze abzufragen.

Na ja, und das Cachemodul hat dann also die bereits bekannten mit den neuen Datensätzen vermischt, die nicht mehr vorhandenen entfernt und dann dieses Paket geliefert.

Alternativ kannst Du im Server auch beim Ändern eines Auftrags dessen PK in einer Liste ablegen und der Cache zieht sich also diese Liste, löscht sie und lädt dann nur die PK dieser Liste (sie enthält ja nur die PK der Datensätze, die verändert wurden).

Vorher: 20s.
Nachher: 0,5s.

Wenn Du allerdings nur am Server rumschrauben kannst, bringen o.g. Ideen natürlich nichts.

[361]

Hallo Furtbichler,

und vielen Dank für Deine Antwort.

Also zwecks Datenmenge: Die ID-Spalten sind character(26) und die Quantity ist ein Integer.

Ansonsten ändern sich öfter mal Datensätze und mal nicht, das ist schwer zu sagen und je Anwenderkreis unterschiedlich. Die Datenschicht hat bereits einen Cache, der allerdings immer wieder die gesamten Daten lädt und nicht selektiv die Änderungen (Manko). Dieser Schritt ist geplant, zeitlich allerdings etwas intensiver. Bevor ich mich an diese Umsetzung mache, wollte ich grundsätzlich erst einmal optimieren. Hierbei ist mir Dank Eurer Antworten einerseits der Teil Server-Resourcen/Optimierung aufgefallen und die Art der Datenabfrage (weniger Daten und selektiv den Datencache refreshen) könnte noch verbessert werden.

Eine Frage an die Postgre-Spezialisten. Besteht die Möglichkeit eine solche "View" vom Server aus schon im Speicher vorzuhalten, sprich eine Art MemTable bei der der Server die Daten in dieser "View" immer aktuell hält und die Abfragezeit dadurch minimiert wird. Wenn ja, wie geht das?

[jobo]

Ich bin kein Postgres Spezialist, daher kann ich Dir auch die ältere Frage an mich nicht beantworten- hast Du selbst schon hinbekommen.

Vielleicht hilft das Stichwort Materialized View weiter. Soweit ich das überflogen hab, ist Postgres da etwas zurück, aber es gibt Anleitungen zur Handarbeit. Schau mal, ob es für Dich passen könnte. Vielleicht mit Hilfe einer kleinen Testtabelle.

http://tech.jonathangardner.net/wiki...rialized_Views
http://wiki.postgresql.org/wiki/Materialized_Views

Das sind einfach die ersten 2 google Treffer zu dem Thema, sieht ganz gut aus, aber ohne Gewähr meinerseits.