Unregelmäßigkeit bei Select-Abfragen

Hallo,

ich hoffe, das ist der richtige Bereich für meine Frage:

Ich habe die Standard-SQL-Operationen und Select auf Zeit pro Datensätze getestet. Beim Select ist eine Unregelmäsigkeit aufgetreten (siehe Anhang ( Die Tabellen sind mit INNER JOIN verknüpft.)).



Wir haben versucht die Konfiguration umzustellen (Arbeitsspeicher uns Co.), aber der Ausschlag blieb an Ort uns Stelle.

Woran kann es liegen, dass der Wert auf einmal so hoch ausschlägt? Kann man irgendwelche Parameter in der Konfiguration einstellen, die diesen Ausschlag nach rechts verschiebt?

Ich hoffe, ihr könnt mir helfen.
Gruß
Lara

Also, ich zumindest habe exakt keine Vorstellung, was Du hier gemessen hast. Vermutlich geht es einigen anderen ähnlich.


Andreas

Ich habe eine Tabelle aus der PostgreSQL Datenbank mit Select abgefragt.

SELECT count(*)
from tab_1 p
WHERE p.id < 3275028

Dann 3 Tabellen, die mit INNER JOIN verknüpft wurden

SELECT count(*)
from tab_1 p
INNER JOIN tab_2 u ON p.user_id=u.user_id
INNER JOIN tab_3 pa ON pa.user_id=u.user_id
WHERE p.id < 2232217

und dann 5 Tabellen

SELECT count(*)
from tab_1 p
INNER JOIN tab_2 u ON p.user_id=u.user_id
INNER JOIN tab_3 pa ON pa.user_id=u.user_id
INNER JOIN tab_4 pm ON pm.user_id=u.user_id
INNER JOIN tab_5 up ON up.user_id=u.user_id
WHERE p.id < 1279920

Ich habe mehrere Durchläufte für 50.000, 100.000, 200.000, 400.000, 800.000 … 2.800.000, 3.200.00 Datensätzen u.s.w. … gemacht und je den Durchschnitt der 3 Methoden in diesem Diagramm dargestellt.

Was meint EXPLAIN zu Deiner Frage?

Andreas

Ich weiß leider nicht, dass du damit meinst.


Wir haben mal den Arbeitsspeicher vergrößert. Aber dass hat die beiden Sprünge nicht nach rechts rücken lassen. Es sind nur alle Werte unmerklich nach unten gesunken.

EXPLAIN zeigt den Abfrageplan. In Verbindung mit ANALYSE bekommt man die Abfrage auch ausgeführt und kann sehen, was genau abläuft. In neueren Versionen kann man auch ersehen, ob z.B. Sortieroperationen im RAM oder auf der Platte erfolgen.

Kurz formuliert: erst Anlaufstelle bei Performance-Analysen ist IMMER das EXPLAIN.

Andreas

Also ich arbeite mit phpPgAdmin 4.2.2 und wenn ich auf Analyse bei einer meiner Tabellen klicke wird mir nur nach einer gewissen Zeit angezeigt, dass es die Analyse durchgeführt hat, aber nicht was da passiert ist.

Habe ich da was übersehen oder hat du was anderes gemeinst?
Ich bin noch PostgreSQL Anfängerin, musst du wissen …

Aber ich danke dir sehr, dass du dir für mein Problem Zeit nimmt. :wink:

Vermutlich macht Dein phpPgAdmin da ein PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: ANALYZE, kein PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Using EXPLAIN.


Andreas

Ich habe das explain mal ausgeführt

EXPLAIN  SELECT  * 
FROM tab_2, tab_1, tab_4, tab_3, tab_5

EDIT

Das ist kein Execution plan von PostgreSQL sondern einer von MySQL.
Bist Du Dir sicher, dass Du PostgreSQL verwendest?

Ups, du hast recht. Ich habe den Befehl im falschen Editor verarbeitet. I-)

Das ist das richtige Ergebnis:

Nested Loop  (cost=39.71..1411746492593230768504832.00 rows=70536754371864697356943360 width=298)
  ->  Nested Loop  (cost=0.00..1011405155936572407808.00 rows=35732904950286071627776 width=256) 
        ->  Nested Loop  (cost=0.00..13018407241416594.00 rows=457217316677364544 width=182)     
              ->  Nested Loop  (cost=0.00..12673432670.08 rows=388414701668 width=147)           
                    ->  Seq Scan on t_user_portal_access  (cost=0.00..1980.72 rows=78172 width=90)
                    ->  Seq Scan on t_source_information_portal  (cost=0.00..112435.19 rows=4968719 width=57)
              ->  Seq Scan on t_user_portal_menu  (cost=0.00..21745.37 rows=1177137 width=35)                
        ->  Seq Scan on t_user  (cost=0.00..1430.53 rows=78153 width=74)                                     
  ->  Materialize  (cost=39.71..59.45 rows=1974 width=42)                                                    
        ->  Seq Scan on t_user_property  (cost=0.00..37.74 rows=1974 width=42)

und dass ist das Ergebnis, wenn ich die Tabellen einzeln aufrufe:

Seq Scan on tab_1 (cost=0.00..112435.19 rows=4968719 width=57)
Seq Scan on tab_2 (cost=0.00..1430.53 rows=78153 width=74)
Seq Scan on tab_3 (cost=0.00..1980.72 rows=78172 width=90)
Seq Scan on tab_4 (cost=0.00..21745.37 rows=1177137 width=35)
Seq Scan on tab_5 (cost=0.00..37.74 rows=1974 width=42)

SELECT * FROM tab_2, tab_1, tab_4, tab_3, tab_5

Du weisst aber schon, dass das Statement - mit Verlaub gesagt - totaler Blödsinn ist?

Das erzeugt ein kartesisches Produkt aus allen 5 Tabellen. Dass das immer langsamer wird, wenn die Tabellen größer werden ist klar.

Verwende doch mal ein realistisches Statement welches die verwendeten Tabellen auch korrekt JOINed

LOL: rows=70536754371864697356943360

Andreas

Ja, kein Wunder, dass das langsam ist.

Obwohl, ich kann dir sicherlich die passende Appliance verkaufen, um das zu beschleunigen.

Ernsthaft: die Anfrage in der Form liest zuviele Daten. Kannst du das einschränken?

Ich will die Anfrage doch gar nicht beschleunigen. Ich will doch nur wissen warum …

… wenn ich eine Tabelle abfrage zwischen 2.400.000 und 2.800.000 Datensätzen

… und wenn ich 3 Tabellen abfrage zwischen 1.200.000 und 1.600.000 Datensätzen …

… es diesen Sprung macht. (siehe Graphik im ersten Post.)

Das es immer langsamer wird, ist mir klar. Ich finde es nur komisch, dass es so sprunghaft passierst. Und warum trifft dieser Sprung nicht bei der Abfrage von 5 Tabellen auf? Warum wird die Abfrage von 3 Tabellen schneller langsam als die von 5?

Versteht ihr mein Problem? Ich will keine Optimierung (vielleicht später). Ich will eine Begründung, die ich vielleicht auch in meine Bachelorarbeit reinschreiben könnte …

Schau Dir doch einfach mal die Ausgaben von EXPLAIN ANALYSE an. Achte dabei auf die Pläne, auf die Ergebnissmengen, auf die geschätzten Kosten etc.

Andreas

Weil Du das kartesische Produkt der Tabellen bildest. Und das wächst exponentiell.

3 Tabellen mit je 100 Datensätzen liefern ein kartesisches Produkt von 1000000 Datensätzen
3 Tabellen mit je 1000 Datensätzen liefern ein kartesisches Produkt von 1000000000 Datensätzen

4 Tabellen mit je 100 Datensätzen liefern ein kartesisches Produkt von 100000000 Datensätzen
5 Tabellen mit je 100 Datensätzen liefern ein kartesisches Produkt von 10000000000 Datensätzen

Klar, dass das irgendwann in Größenordungen geht wo es wirklich langsam wird.

Wie gesagt: nimm ein realistisches Statement bei dem ein vernünftiger JOIN verwendet wird und kein kartesisches Produkt erzeugt wird.

Also ich weiß nicht, ob ich euch nicht verstehe, ober ihr mich nicht versteht. Meine Select-Abfragen werden durch eine Where-Bedingung eingeschränkt. Ich frage nur 3 bis 5 Mio. Datensätze ab. Ich will doch nur den Anstieg der Abfragezeit darstellen.

Hier ein Ausschnitt meiner Tabelle für PostgreSQL: “Select - 3 Tabellen mit INNER JOIN verknüpft”

       
800.000   1.200.000   1.600.000   2.000.000   Datensätze
-----------------------------------------------------------------
   1.180      1.507      4.540     4.860
   1.180      1.566      4.558     4.440       
   1.185      1.507      4.547     4.661     
[...] 
-----------------------------------------------------------------
   1.211      1.513      4.995     4.679        Durchschnitt in ms

In Anhang habe ich ein Diagramm hoch geladen, dass alle Select-Abfragen aus 4 Datenbanken zeigt. (Etwas unübersichtlich, ich weiß. MySQL liegt ganz unten auf dem Boden. Nach dem Cachen wächst es selbst bei 15 Mio. Datensätzen nicht an.) Aber die meisten Abfragen verlaufen Linear - nur PostgreSQL springt.