Performance Abfrage über View

Hallo,

habe folgendes Problem.

Ausgangslage:

Tabelle 1: Artikel
artikel_id, sonstige Artikeldaten

Tabelle 2: bestellte Artikel
artikel_id, bestellung_id, sonstige Daten wie Menge usw.

Sicht 1:
Verbindung aus Tabelle 1 + 2 über artikel_id

Abfrage über Sicht 1 (bestellung_id = xxx)

Bei vielen Datensätzen in der Tabelle 2 ist das wohl nicht die beste Lösung.

Habe wenig Ahnung von Funktionen, aber eine Funktion die zuerst die Abfrage über Die Tabelle 2 macht, und mit dem Ergebnis die Verbindung zu Tabelle 1 ist wahrscheinlich besser.

Da mir die Rückgabe mehrerer Datensätze aus einer Funktion auch nicht so leicht erscheint, wollte ich euch mal Fragen welche Richtung ich einschlagen soll. Oder gibt es sogar noch einen anderen Weg?

Gruß Christian

Warum sollte das, was Du in einer Funktion nachzuprogrammieren versuchst, schneller sein als
das, was die DB eh macht?
Falls Du das schaffst: Respekt. Und helf bitte, die DB weiterzuentwicklen.
Aber ich glaube eher, Du schaffst das nicht.

Und ja: bitte schaue Dir EXPLAIN an, und beschäftige Dich mit Indexen. Und allgemein:
das, was Du da machst, nennt sich ‘Normalisierung’ und ist exakt richtig. Und eine DB wie
PG ist darauf getrimmt, genau das schnell und elegant zu realisieren.

Ähm, jetzt les ich die Frage grad noch mal:

Du hast einen View, der Dir deine 2 Tabellen vereint, ein select … from view; ohne Eingrenzung bringt da sehr viele Rows. Du sorgst Dich, ob eine Limitierung beim select auf den View nicht
erst die komplette Menge liefert und das dann eingrenzt, oder?

Nein, tut es nicht.

Schaue Dir dennoch den Explain an!

Andreas

Hi Andreas,

habe folgende Ausgansgslage

CREATE OR REPLACE VIEW v_be_artikel AS 
 SELECT t_be_artikel.be_artikel_id, t_be_artikel.be_artikel_menge, t_be_artikel.be_artikel_bestellung, t_be_artikel.be_artikel_artikel, t_artikel.artikel_id, t_artikel.artikel_text
   FROM t_be_artikel, t_artikel
  WHERE t_be_artikel.be_artikel_artikel = t_artikel.artikel_id;

Wenn ich folgende Abfrage mache

EXPLAIN SELECT * FROM v_be_artikel WHERE be_artikel_bestellung = 1

erhalte ich diese Ergebnis

“Hash Join (cost=27.27…39.42 rows=5 width=545)”
" Hash Cond: (“outer”.artikel_id = “inner”.be_artikel_artikel)"
" -> Seq Scan on t_artikel (cost=0.00…11.40 rows=140 width=520)"
" -> Hash (cost=27.25…27.25 rows=7 width=25)"
" -> Seq Scan on t_be_artikel (cost=0.00…27.25 rows=7 width=25)"
" Filter: (be_artikel_bestellung = 1)"


Habes auch mit der Funktion versucht:

CREATE OR REPLACE FUNCTION f_bestellung(int4)
  RETURNS SETOF t_be_artikel AS
$BODY$
	SELECT * FROM t_be_artikel WHERE be_artikel_bestellung = $1;
$BODY$
  LANGUAGE 'sql' VOLATILE;

Wenn ich dann den Explain mache:

SELECT * FROM f_bestellung(1), t_artikel WHERE artikel_id = be_artikel_artikel

kommt das raus

“Hash Join (cost=11.75…38.67 rows=700 width=545)”
" Hash Cond: (“outer”.be_artikel_artikel = “inner”.artikel_id)"
" -> Function Scan on f_bestellung (cost=0.00…12.50 rows=1000 width=25)"
" -> Hash (cost=11.40…11.40 rows=140 width=520)"
" -> Seq Scan on t_artikel (cost=0.00…11.40 rows=140 width=520)"

Beides führt zum gleichen Ergebnis.

Das letztere scheint mir etwas schneller zu sein, und jetzt sind in meinen Tabellen fast noch keine Daten, werden wenn es funktioniert aber mal ziemlich viele.

Bin ich da auf dem Holzweg?

Danke Christian

Vermutlich, weil Du das, was ich schrieb, vermutlich nich zu Ende gelesen hast. Hint:
Indexe regeln, hier auf dem Feld [u]‘artikel_id’. Ich bin mir fast sicher, daß da keiner ist…

Andreas, der sich wundert, warum das nun unterstrichen ist und sich noch nicht so recht wohlfühlt mit diesen tausend blöden Smilys und dem Kram hier… Mail/News ist besser.
[/u]

Hi Andreas,

Vermutlich, weil Du das, was ich schrieb, vermutlich nich zu Ende gelesen hast. Hint:

Hab ich jetzt schon mehrmals, Tomaten auf den Augen.

Den Index habe ich jetzt gesetzt, und zwar auf dem Feld t_artikel.artikel_id. Cool, bringt eine deutliche Verbesserung in meiner 2. Lösung mit der Funktion(“Hash Join (cost=1.20…19.50 rows=80 width=545)”), in der 1. Lösung mit der Sicht nur minimal (“Hash Join (cost=27.27…28.52 rows=1 width=545)”).

Entweder ich bin zu blöd und versteh da was falsch, oder die Lösung mit der Funktion ist schneller.

Ich hoffe ich nerv dich nicht.
Christian

Vielleicht liegt das an dem Wirrwarr, das Du angestellt hast.

Der Index ist auf ein feld in der Tabelle t_artikel, der View basiert auf t_be_artikel und nennt
sich v_be_artikel und die Funktion f_bestellung such in t_be_artikel.

Ordne das mal sinnvoll, dann wirst Du das Problem sicherlich selber finden.

Im übrigen: Angaben wie cost=foo…bar besagen, daß der erste Wert nach foo kommt und die
Aktion bis bar dauert. Wenn die Differenz zwischen foo und bar nicht allzu groß ist und foo
schon fast so groß wie bar ist, dann liegt das Problem i.d.R. bereits vorher.

Andreas

Hallo Christian,

es gibt noch genügend andere Mitglieder in diesem Forum, bei denen Du nicht den Eindruck haben wirst, das Du nevst. Also herzlich willkommen.



mfg

fristo

Hi,

es gibt noch genügend andere Mitglieder in diesem Forum, bei denen Du nicht den Eindruck haben wirst, das Du nevst. Also herzlich willkommen.

Den Eindruck habe ich auch nicht, wollte nur sicher gehen.

Ich stell mich nur ziemlich blöd an.

Mein Menschenverstand sagt mir, es geht schneller, wenn ich zuerst die 5 Artikel einer Bestellung, aus einer Liste mit 1000 Artikeln, raussuche und dann die restlichen Daten zum Artikel verbinde. Anstatt mit einer View erst alle 1000 Artikel verbinde und dann die 5 der Bestellung raussuche.

Wenn ich meine zwei Möglichkeiten in SQL ausprobiere bestätigt ein Explain meine Vermutung, was mache ich falsch? Meiner Meinung nach dauert es solange weil der View erst neu erstellt wird wenn ich ihn aufrufe, aber ich verstehe auch nichts von der Technik hinter PG.

Gruß

Christian

Dein Problem könnte u.U. zielführender beantwortet werden, wenn Du weniger auf irgend
welche Windows-Luser hören würdest.

Du hast in all Deinen Texten hier 'ne Menge tables und so genannt, ich hab grad keine Lust,
das ins Detail nachzuvollziehen.
Fakt ist, daß JOINs, sofern die Indexe stimmen, recht flott funktionieren. Mal ein Beispiel aus
meiner Firma:

scholl=*# explain analyse select a.ab, a.pos, a.uf, b.bezeichnung from bde_meldungen a left join maschine b on a.maschine=b.nr where a.datum=‘2006-08-30’;
QUERY PLAN

Hash Left Join (cost=17.16…7733.87 rows=2403 width=55) (actual time=1.231…20.141 rows=2620 loops=1)
Hash Cond: (“outer”.maschine = “inner”.nr)
→ Bitmap Heap Scan on bde_meldungen a (cost=15.41…7696.08 rows=2403 width=27) (actual time=0.807…7.252 rows=2620 loops=1)
Recheck Cond: (datum = ‘2006-08-30’::date)
→ Bitmap Index Scan on idx_datum (cost=0.00…15.41 rows=2403 width=0) (actual time=0.781…0.781 rows=2620 loops=1)
Index Cond: (datum = ‘2006-08-30’::date)
→ Hash (cost=1.60…1.60 rows=60 width=36) (actual time=0.393…0.393 rows=60 loops=1)
→ Seq Scan on maschine b (cost=0.00…1.60 rows=60 width=36) (actual time=0.010…0.229 rows=60 loops=1)
Total runtime: 25.165 ms
(9 rows)


Der Umbruch ist ein Desaster, aber ich bin mit dieser Weboberfläche noch nicht wirklich firm, daher sorry. Die table bde_meldungen hat ca. 1,7 Millionen Zeilen, die table maschine ein paar Dutzend. Genuzte Indexe sind auf bde_meldungen hier auf datum, und natürlich auf maschine(nr).

(ähm, sehe grade, letzteres wohl nicht, weil table zu klein wohl)


Faustregel: einfach schauen, worauf du filterst, und worauf du verbindest. Darauf Indexe.

Also, noch einmal: ordne einfach mal Deine Umgebung, überleg Dir, was Du wirklich brauchst, setz sinnvolle Indexe und schau Dir EXPLAIN an.


Andreas

Hi Andreas,

eigentlich lasse ich mich nicht gern auf Grundsatzdiskussionen in Foren ein, die Threads sind zwar immer recht lang aber rauskommt wenig.

Was macht das für einen Unterschied Windows oder Linux? Ausser der Mentalen?

Hast Du dich mit deinen Antworten jetzt immer auf die Performance der View bezogen, oder auch mal über meinen anderen Ansatz mit der Funktion nachgedacht?

Gruß
Christian



Was macht das für einen Unterschied Windows oder Linux?

Eine recht großen. Aber das ist hier Offtopic.

Deinen ‘Ansatz’ mit der Funktion hab ich gesehen, aber eine einzeilige Funktion in SQL, die nur einen SELECT enthält,
und einem VIEW machen nicht wirklich einen Unterschied.

Interessanter wäre zu wissen, ob Du die Tabellen gescheit angelegt hast, also so mit Fremdschlüssel, passenden Datentypen, passenden Indexen.
Ein VIEW ist nix weiter als ein hinterlegtes SQL, welches bei Abruf des VIEWS ausgeführt wird. Es ist genausoschnell
wie das dem VIEW zugrundeliegende SQL, würde man es direkt ausführen.


Andreas

Hi Andreas,

Ein VIEW ist nix weiter als ein hinterlegtes SQL, welches bei Abruf des VIEWS ausgeführt wird. Es ist genausoschnell
wie das dem VIEW zugrundeliegende SQL, würde man es direkt ausführen.

Das genau ist meine These:

Wenn ich einen Verbund mit der kompletten Tabelle durchführe und dann die benötigten Datensätze filtere, dauert es länger, wie wenn ich den Verbund auf die gefilterten Datensätze durchführe.

Deshalb die einzeilige Funktion, weil sich die leichter in einem Select * From funktion, tabelle Where x=y verarbeiten lässt.

Ich werde wenn die Anwendung mal ein paar Daten produziert hat das ganze nocheinmal testen, dann kommt es ja auf.

Gruß Christian

Hallo byte,

wenn du einen Index auf den Spalten definierst über die der Join ausgeführt wird, sollte deine Funktion, die einfach die Menge der zu joinenden Datensätze einschränkt keinen Geschwindigkeitsvorteil bringen, da der Query Optimizer versucht deinen SQL-Befehl so umzustrukturieren, da


Allerdings entscheidet der Query optimizer von postgres ob ein Index eingesetzt werden soll oder nicht. Dabei stützt sich der Optimizer auf Statistiken, die für jede Tabelle geführt werden. Wenn du viele Datensätze in einer Tabelle hast und trotzdem beim Join kein Index eingesetzt wird, sind warscheinlich die Statistiken nicht aktuell (du kannst die Statistiken aktualisieren mit dem Befehl ANALYSE.) oder du hast Indizes für die falschen Attribute erstellt.