Geschwindigkeit optimieren

Hey,

wie optimiere ich die Geschwindigkeit einer Abfrage?
Ist es schneller wenn ich möglichst viele Sub selects in einem View habe oder mit einer Abfrage auf mehrere Views zugreife?
bzw. kapier ich das mit den Prozeduren nicht so ganz, kann ich eine SQL Prozedur schreiben, die mir einfach immer die Daten “Komplettiert” und so zu sagen ein “datawarehouse” macht?

Gruß BoOnOdY

Für Performance Optimierungen ist der Befehl EXPLAIN sehr gut geeignet. er zeigt dir den kompletten Plan, wie die Datenbank deine Anfrage ausführt. Daran kann man dann erkennen, wo Schwachstellen sind und ggf. optimieren.

Ein View ist nichts anderes als eine gespeicherte Abfrage. PostgreSQL setzt das dann intern sowieso in die gespeicherte Abfrage um. Kannst du ebenfalls mit EXPLAIN ausprobieren.

Was meinst du mit “komplettiert” in diesem Zusammenhang? Eine Stored Procedure ist eine Funktion wie in jeder anderen Programmiersprache auch. Man kann ihr Daten übergeben, mit diesen Daten arbeiten und Ergebnisse zurückbekommen. Die Möglichkeiten sind jedoch sehr viel weiter gefasst als in reinem SQL.

Zuzüglich zu dem von ads:

Wenn Du eine Abfrage an die DB richtest, ist einer der ersten Schritte der DB, dazu einen Abfrageplan zu erstellen. Dazu untersucht es Dinge wie den Aufbau der beteilgten Tabellen, die Art der Abfrage, welche Indexe vorhanden sind und evtl. genutzt werden können, Statistiken über die Daten in den Tabellen und einiges mehr. Von der Wahl des richtigen Abfrageplanes hängt maßgeblich die Geschwindigkeit ab, mit der die Abfrage ausgeführt wird.

Damit Du als DBA nicht als blinde Kuh daneben stehst, gibt es den Befehl EXPLAIN bzw. EXPLAIN ANALYSE. Und als DBA wirst Du um diesen Befehl nicht drum herum kommen, sondern Dich damit anfreunden.

15:21 < akretschmer> ??explain
15:21 < pg_docbot_adz> For information about 'explain' see:
15:21 < pg_docbot_adz> http://www.gtsm.com/oscon2003/toc.html :: http://www.postgresql.org/docs/current/static/sql-explain.html ::
                       http://www.postgresql.org/docs/current/static/performance-tips.html#using-explain
15:21 < pg_docbot_adz> http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi

schaue Dir diese Links an, da ist einiges zu erklärt.


Andreas

Ja also komplettieren heißt :
ich bekomm einen Datensatz rein
vergleiche ihn mit Datensätzen aus einer anderen Tabelle und möchte dann dieses Vergleichergebnis noch dazumachen. Wo finde ich denn noch ein bisschen was zu dem Prozeduren schreiben in PostgreSQL ?

Also, dass immer wenn ich meine Datensätze mit meinen Phytonscript einlese, soll die Datenbank diesen Vergleich machen und das Ergebniss in eine neue Tabelle schreiben.

Ist es da besser alle Datensätze noch mal neu zu schreiben oder einfach nur den Vortlaufenden Primary Key zu nehmen und dahinter diese Analyse zu schreiben?


Wenn ich das im Moment mit so Abfragen mache, dauert das ewig ( hab an die 20 000 Datensätze analysieren wollen).

Und ich meine mit so ner einfach vervollständigten Liste wird das doch um einiges schneller gehen, oder?
Gruß BoOnOdY



PS:
Danke, dass ihr mir eure Erfahrung zu Teil werden lasst :slight_smile:

Das Handbuch zu PG ist lesenswert, z.B. ab hier:

Was Du derzeit für Abfragen auf lächerliche 20K Rows machst und warum das ‘ewig’ dauert, kann Dir hier ohne ein EXPLAIN ANALYSE Deiner Abfrage keiner so recht genau sagen, auch nicht, ob man es schneller machen könnte.


Andreas

Also ich hab mich jetzt beschlossen mich ma mit Triggern und Prozeduren zu beschäftigen.

Quelltext Frage:

Hat jemand zufällig einen Trigger oder eine Prozedur parat, die Datensätze beim einfügen ergänzt? Vieleicht sogar mit einem Vergleich aus einer anderen Tabelle. Würd gerne ma ein bisschen Syntax sehen, dass ich mich daran orientieren kann. Sonst les ich mich erst ma rein bevor ich noch groß dumme Fragen stelle. :astonished:

Gruß BoOnOdY

Das findest Du auch in der normalen Doku. Aber ich glaube fast, das würde auch ohne aufwendige Trigger und Funktionen gehen, sondern mit purem SQL. Aber das ist mal nur eine wilde Vermutung. Auch vermute ich ja, daß Dir Foreign Keys ein Fremdwort sind, aber auch das ist eine pure Vermutung…

Andreas

So hier ist ma so ein Explain:

[size=84]
QUERY PLAN
"HashAggregate (cost=598819.41..600115.32 rows=11 width=55)"
" -> Hash Join (cost=186144.94..598819.27 rows=11 width=55)"
" Hash Cond: (""outer"".dialcode = (subplan))"
" -> Seq Scan on destination (cost=0.00..99.92 rows=3492 width=28)"
" -> Hash (cost=186144.91..186144.91 rows=11 width=35)"
" -> Hash Join (cost=102505.73..186144.91 rows=11 width=35)"
" Hash Cond: ((subplan) = ""inner"".""location"")"
" -> Seq Scan on call (cost=0.00..17.10 rows=710 width=19)"
" -> Hash (cost=102505.72..102505.72 rows=3 width=80)"
" -> Hash Join (cost=85306.90..102505.72 rows=3 width=80)"
" Hash Cond: (""outer"".""?column1?"" = ""inner"".""location"")"
" -> HashAggregate (cost=83639.08..100835.68 rows=146 width=15)"
" -> Seq Scan on call (cost=0.00..83635.53 rows=710 width=15)"
" SubPlan"
" -> Limit (cost=117.77..117.77 rows=1 width=8)"
" -> Sort (cost=117.77..117.81 rows=17 width=8)"
" Sort Key: char_length(dialcode)"
" -> Seq Scan on destination (cost=0.00..117.42 rows=17 width=8)"
" Filter: ($1 ~~ (dialcode || '%'::text))"
" -> Hash (cost=1667.81..1667.81 rows=3 width=40)"
" -> HashAggregate (cost=1314.43..1667.78 rows=3 width=15)"
" -> Seq Scan on call (cost=0.00..1314.37 rows=11 width=15)"
" Filter: (dauer > 0)"
" SubPlan"
" -> Limit (cost=117.77..117.77 rows=1 width=8)"
" -> Sort (cost=117.77..117.81 rows=17 width=8)"
" Sort Key: char_length(dialcode)"
" -> Seq Scan on destination (cost=0.00..117.42 rows=17 width=8)"
" Filter: ($1 ~~ (dialcode || '%'::text))"
" SubPlan"
" -> Limit (cost=117.77..117.77 rows=1 width=8)"
" -> Sort (cost=117.77..117.81 rows=17 width=8)"
" Sort Key: char_length(dialcode)"
" -> Seq Scan on destination (cost=0.00..117.42 rows=17 width=8)"
" Filter: ($0 ~~ (dialcode || '%'::text))"
" SubPlan"
" -> Limit (cost=117.77..117.77 rows=1 width=8)"
" -> Sort (cost=117.77..117.81 rows=17 width=8)"
" Sort Key: char_length(dialcode)"
" -> Seq Scan on destination (cost=0.00..117.42 rows=17 width=8)"
" Filter: ($0 ~~ (dialcode || '%'::text))"
" -> Limit (cost=117.77..117.77 rows=1 width=8)"
" -> Sort (cost=117.77..117.81 rows=17 width=8)"
" Sort Key: char_length(dialcode)"
" -> Seq Scan on destination (cost=0.00..117.42 rows=17 width=8)"
" Filter: ($0 ~~ (dialcode || '%'::text))"
[/size]

es sieht bestimmt nicht besonders aus, da ich Views in Views benutze, muss das in subselects packen ich weiß … :frowning:

Das Views in Views ist nicht wirklich ein Problem, problem ist eher:

  • ohne das zugrundeliegende SELECT ist es schwer, den Sinn zu verstehen
  • ich sehe jede Menge SEQ-Scans, vermutlich hast Du keine Indexe
  • ein EXPLAIN ANALYSE hätte auch noch angezeigt, ob der Planner aktuelle Statistiken hat
  • die Ausgabe möglichst so in der verschachtelten Struktur lassen, wie es vom Explain kommt

Mal auf die Schnelle als Beispiel:

scholl=# explain analyse select uf, ab, pos from bde_meldungen where maschine = 1200 and datum = '2006-08-30';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bde_meldungen  (cost=2.70..452.98 rows=117 width=23) (actual time=0.107..0.457 rows=119 loops=1)
   Recheck Cond: ((maschine = 1200) AND (datum = '2006-08-30'::date))
   ->  Bitmap Index Scan on idx_combi  (cost=0.00..2.70 rows=117 width=0) (actual time=0.086..0.086 rows=119 loops=1)
         Index Cond: ((maschine = 1200) AND (datum = '2006-08-30'::date))
 Total runtime: 0.740 ms
(5 rows)

Da kann man dann sehen, daß Indexe da sind und das der Planner recht gut schätzt (117 Rows geschätzt, 119 real)


Andreas

Also falls das hilft sind hier mal meine Views die ich benutze.

der View call_destination ist der den ich bei dem explain aufgerufen habe


CREATE OR REPLACE VIEW asr AS 
 SELECT (call_zustande.anzahl::numeric(10,2) / call_alle.count::numeric(10,2) * 100::numeric)::numeric(10,2) AS "numeric", call_zustande."location"
   FROM call_zustande, call_alle
  WHERE call_zustande."location" = call_alle."location";
 
 
CREATE OR REPLACE VIEW call_alle AS 
 SELECT call_view."location", count(*) AS count
   FROM call_view
  GROUP BY call_view."location";
 
CREATE OR REPLACE VIEW call_desti AS 
 SELECT grouped_calls.dauer, ( SELECT destination.dialcode
           FROM destination
          WHERE grouped_calls.ausgangsnummer ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1) AS "location"
   FROM ( SELECT call.ausgangsnummer, call.dauer
           FROM call) grouped_calls;
 
 
CREATE OR REPLACE VIEW call_destination AS 
 SELECT call_desti."location", destination.name, count(*) AS count, sum(call_desti.dauer) / 60000 AS dauer, max(asr."numeric") AS sum
   FROM destination, call_desti, asr
  WHERE destination.dialcode = call_desti."location" AND asr."location" = call_desti."location"
  GROUP BY call_desti."location", destination.name;
 
 
CREATE OR REPLACE VIEW call_zustande AS 
 SELECT count(*) AS anzahl, call_view."location"
   FROM call_view
  WHERE call_view.dauer > 0
  GROUP BY call_view."location";

ich hab keine Idexe, weil mein SQL buch meint, dass bei dem Typ Text, den ich für den Vergleich ja brauche kein Index funktionieren würde. Stimmt das?

Mir ist das grad zu anstrengend, diese geschachtelten VIEWS zu durchdringen.
Hab ja auch schon längst Feierabend :wink:
Vielleicht zeigts Du uns mal Deine Ausgangstabellen und was Du konkret wissen willst.


Zu den Indexen:

Das geht schon. (aber bei String-Typen mit diversen Einschränkungen und Problemen)

test=*# \d foo
    Table "public.foo"
 Column | Type | Modifiers
--------+------+-----------
 w      | text |
Indexes:
    "idx_foo" btree (lower(w) varchar_pattern_ops)

Der Index wurde erstellt mit:

test=# create index idx_foo on foo (lower(w) varchar_pattern_ops);
CREATE INDEX

Eine Suche nutzt nun den Index:

test=*# explain analyse select * from foo where lower(w) like 'help%';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=4.71..333.12 rows=284 width=32) (actual time=0.320..0.394 rows=17 loops=1)
   Filter: (lower(w) ~~ 'help%'::text)
   ->  Bitmap Index Scan on idx_foo  (cost=0.00..4.71 rows=284 width=0) (actual time=0.267..0.267 rows=17 loops=1)
         Index Cond: ((lower(w) ~>=~ 'help'::character varying) AND (lower(w) ~<~ 'helq'::character varying))
 Total runtime: 0.567 ms
(5 rows)

Time: 3.634 ms

Die Tabelle enthält auch ein paar Datensätze:

test=*# select count(1) from foo;
 count
-------
 56840
(1 row)

(aus irgend einer Wörterbuchdatei)

Zu dem Index: schau Dir mal PostgreSQL: Documentation: 8.1: Operator Classes
an. So wirklich firm bin ich da bei Indexen auf CHAR-Spalten nicht, weil noch nie benötigt.


Andreas

Mir ist grad schwindlig.

Dein View asr ist eine Abfrage auf call_zustande, call_alle. Diese beiden beruhen wiederrum beide
auf call_view. Und Du prüfst auf call_zustande.“location” = call_alle.“location”, was aber irknwie
beides aus call_view.“location” kommt.

Diesen View nutzt Du dann mit destination, call_desti im View call_destination, wobei call_desti wieder aus destination ermittelt wird. Du erzeugst damit offensichtlich enorme Zwischenresultate.

Mein Rat: vergesse die Views, schaue auf die originalen Tabellen und arbeite damit. Irknwie drehst Du Dich da im Kreise…


Andreas

Ja ich dreh mich echt im Kreis.

Ich schreib morgen ma genau und klar auf was ich eigentlich will und dann sagt ihr mir was ich falsch gemacht habe, wenn ich nicht selbst auch was finde, okay?

Gruß BoOnOdY :slight_smile:

Was möchte ich erreichen?

  • Ich möchte aus einer Tabelle mit folgendem Plaintext:
identify serial NOT NULL,
callrefnummer text NOT NULL,
datum date,
uhrzeit time,
eingangsport int4,
ausgangsport int4,
eingangsnummer text,
ausgangsnummer text,
dauer int4,
releasecode text,
CONSTRAINT call_pkey PRIMARY KEY (identify)

die ausgangsnummer mit den dialcodes aus dieser Tabelle vergleichen

 identify serial NOT NULL,
dialcode text,
name text,
CONSTRAINT destination_pkey PRIMARY KEY (identify)

dazu benutze ich diesen View

 CREATE OR REPLACE VIEW call_desti AS 
SELECT grouped_calls.dauer, ( SELECT destination.dialcode
FROM destination
WHERE grouped_calls.ausgangsnummer ~~ (destination.dialcode || '%'::text)
ORDER BY char_length(destination.dialcode) DESC
LIMIT 1) AS "location"
FROM ( SELECT call.ausgangsnummer, call.dauer
FROM call) grouped_calls;

So weit so gut. nun habe ich alles was ich brauche :
Die Daten aus Call, Destination und schließlich die zusammengeführten Daten aus call_desti, damit ich die Destination der Calls bestimmen kann.

nun nützt mir eine riesige Liste natürlich nichts, ich will sie Gruppieren:

dazu benutze ich dann

 CREATE OR REPLACE VIEW call_destination AS 
 SELECT call_desti."location", destination.name, count(*) AS count, sum(call_desti.dauer) / 60000 AS dauer, max(asr."numeric") AS sum
   FROM destination, call_desti, asr
  WHERE destination.dialcode = call_desti."location" AND asr."location" = call_desti."location"
  GROUP BY call_desti."location", destination.name;

Doch nun brauche ich einen etwas Komplizierteren Wert, der sich “ASR” nennt. er bedeutet einfach :
Einträge die eine nicht leere Dauer-Zeile haben geteilt durch alle Einträge einer Destination.

Also rechne ich diesen Wert in den restlichen views aus.

 
CREATE OR REPLACE VIEW asr AS 
 SELECT (call_zustande.anzahl::numeric(10,2) / call_alle.count::numeric(10,2) * 100::numeric)::numeric(10,2) AS "numeric", call_zustande."location"
   FROM call_zustande, call_alle
  WHERE call_zustande."location" = call_alle."location";
 
 
CREATE OR REPLACE VIEW call_alle AS 
 SELECT call_view."location", count(*) AS count
   FROM call_view
  GROUP BY call_view."location";
 
CREATE OR REPLACE VIEW call_zustande AS 
 SELECT count(*) AS anzahl, call_view."location"
   FROM call_view
  WHERE call_view.dauer > 0
  GROUP BY call_view."location";

Ich möchte eigentlich so gut wie alle Daten von oben mitnehmen, dass heißt ich brauche eigentlich eine Tabelle in der der “View” gemacht worden ist und zwar pro Eintrag einfach die Zeile “Destination” angefügt ist und dann wäre ich sorgenfrei.
Dann würde das alles super schnell laufen und ohne Probleme.
Daher wollte ich ja das mit den Triggern und Prozeduren machen, also mir einfach eine Prozedur bauen die sagt:
Wenn neue Daten eingetragen werden,
dann mache den “View “call_desti”” und schreibe mir das Ergebnis dieses Views in eine Zeile dahinter die Destination heißt.
so würden alle meine Felder mir zur Auswertung erhalten bleiben, und ich könnte vor allem das lästige “like” los werden, dass so lange braucht und nur noch mit
“Where =” arbeiten und Indexen.

So verstanden was ich meine? Ich möchte am Tag mehrere verschiedene auswertungen dieses Views machen, daher wäre mir eine ergänzte Tabelle lieber.

Gruß BoOnOdY

Etwas besser.

Ich vermute mal, die Tabellen sind call und destination. Leider fehlt die Definition von call_view.

Die Kosten für call_desti würden sich schon mal verringern, wenn für die Sortierung nach
char_length(destination.dialcode) ein Index bestehen würde:

create index idx_sort on destination (char_length(dialcode));

(ich hab mal die Tables erzeugt und je 10.000 Testdaten reingeworfen…)
Allerdings erzeugt dieser view wegens dem

FROM ( SELECT call.ausgangsnummer, call.dauer
           FROM call) grouped_calls;

so viele Rows wie in call sind, und dazu macht er jeweils das

SELECT destination.dialcode
           FROM destination
          WHERE grouped_calls.ausgangsnummer ~~ (destination.dialcode || '%'::text)
          ORDER BY char_length(destination.dialcode) DESC
         LIMIT 1) AS "location"

Bereits an diesem View hast Du quasi verloren. Ist das, was dieser View liefert, wirklich gewollt?

Andreas

Also was will ich mit dieser Abfrage :

Ich möchte erreichen dass ich eine Summe der Dauer und die Anzahl der Datensätze Pro identifziertem Dialcode habe.

Also ich möchte das erreichen, was ich in call.destination abfrage.


Meinst du, dass wenn ich gleich die Summe der Dauer nehme, anstatt die einzelnen Dauer ich Zeit spare?

Ich bin auf Arbeit und hab nicht den Nerv, durch Deine Tables und Schemas zu steigen. Mir sind da bei Dir einige Dinge nicht ganz klar, z.B. mit der Gruppierung der Dialcodes etc.

Fakt ist, daß solche Regex-Vergleiche recht teuer sind. Womöglich kommst Du besser, du untergliederst Deine Dialcodes in numerische Gruppen, um besser zusammengehöhrige Datensätze gruppieren zu können.


Andreas

okay danke :slight_smile:


Und weil das ja so teuer ist werd ich ma gucken, wie ich einfach einen Trigger bauen kann, der mir meine Tabelle mit dem dialcode vervollständigt wenn ich die Daten includiere, dann wär das Zeitproblem ja schon ma Minimiert.

Danke für alles :slight_smile:

Da die Anfragen und Datenbankstruktur, wie akretschmer schon sagt, mittlerweile ziemlich undurchsichtig wird, wäre es nicht besser, noch mal bei Null zu starten und zu schauen, wie man das eleganter hinbekommt?

Nur mal so als Idee …

jop hab ich ja jetzt mit dem Trigger

http://www.pg-forum.de/showthread.php?t=1088


Gruß BoOnOdY