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

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.