Abweichung der Ausführungszeit bei INSERT-Befehlen

Hallo zusammen,

ich arbeite gerade an einer Fragestellung, welche Performanzvergleiche verschiedener SQL-Operationen auf einer PostgreSQL-Datenbank (9.5) umfasst.
Dabei messe ich die Ausführungszeit an zwei Punkten, nämlich auf Client-Seite (Java) und auf Datenbankseite (Execution time einer Ausführung mittels EXPLAIN ANALYZE). Die Befehle werden dabei häufig (ca. 1.000 - 10.000 mal) ausgeführt und der Mittelwert der Messungen verwendet.
Nun stehe ich jedoch vor dem Problem, dass die Ergebnisse der beiden Zeitmessungen bei SELECT- und UPDATE-Befehlen zwar akzeptabel beieinander liegen, bei INSERT-Befehlen jedoch durchgehend um Größenordnungen voneinander abweichen (Beispielmessung: ca. 0,05ms im Execution Plan vs. 4ms in der Client-Messung). Es handelt sich um Minimalbeispiele für SQL-INSERTs, keine komplexen Queries.
Dabei ist es egal, ob das Statement für die Client-Messung ebenfalls mit EXPLAIN ANALYZE ausgeführt wird, oder “pur”.
Den selben Effekt konnte ich auch mit verschiedenen Datenbankclients wie DBeaver nachvollziehen.

Nun meine Frage: Wie ist dieser extreme Unterschied (ausschließlich) bei INSERT-Befehlen zu erklären? :confused:

Vielen Dank
Chris95

kann es sein, daß da auf Java-Seite noch Prepare oder so dazukommt? Und welcher Wert ist denn realer bzw. plausibler?

Der prepare()- und close()-Aufruf erfolgt auf Clientseite vor bzw. nach der eigentlichen Zeitmessung, welche ausschließlich den execute()-Aufruf umfasst.
Allerdings habe ich es in Java sowohl mit Statements, als auch PreparedStatements versucht, der Effekt ist in beiden Fällen der selbe.

Welcher Wert realer/plausibler ist, ist aus meiner Sicht leider nicht vollständig zu beantworten. Auf Client-Seite wird ja eine Zeitmessung durchgeführt, die auch eine “reale” Anwendung so implementieren würde (was durch verschiedene Datenbankclients bestätigt wird). Andererseits sind die dort gemessenen 3-4ms für die Durchführung eines einzigen INSERT-Befehls laut einem Kollegen erstaunlich lange, was eher für die Korrektheit der EXPLAIN ANALYZE - Messung spricht.

Dann gehen wir mal bis zum Beweis des Gegenteils davon aus, daß Explain analyse korrekt ist, okay?

Ich habe keine Ahnung von der Technik, die Postgres beim Insert verwendet, also Plattenzugriffe usw., aber was unterscheidet ein Insert von Update und Select? Es ist die Notwendigkeit Platz auf der Platte zu allokieren. Das könnte solche Effekte mit sich bringen oder?
Ich würde mangels Intimkenntnis ein paar Tests machen. Vielleicht findet man ein Muster, wenn man konkrete ID und Insertzeiten auf den Zeitstrahl legt, die Satzgröße mit der Blockgröße vergleicht usw usf.
Sicher gibt es auch Literatur zu den eingesetzten Verfahren. Ggf. spiegelt sich diese Technik dann auch je Betriebssystem, Filesystem, Cacheverfahren, HDD/SDD in den Tests wieder.

Ein Update ist letztlich ein Delete+Insert, aber das ist hier nicht relevant. PostgreSQL schreibt erst einmal nur ins WAL und macht ein fsync. Das ein Select schneller ist (vor allem, wenn Daten in shared buffers sind) ist normal, aber ein Insert unterscheidet sich dann nicht grundsätzlich von einem Update, beide schreiben ins WAL und machen ein fsync, was teuer im Sinne von benötigter Zeit ist.

Danke schonmal für die interessanten Ansätze!

Mir leuchtet ein, dass INSERTs / UPDATEs grundsätzlich teurer sind als rein lesende Zugriffe, aufgrund von fsync usw.
Jedoch: Dieser “teure” Bestandteil der Operationen sollte ja grundsätzlich in beiden von mir verwendeten Messpunkten ausgeführt werden, sodass die signifikante Differenz der Messungen (bzgl. des gleichen Statements) dadurch nicht erklärbar ist.

Es sei denn, PostgreSQL berücksichtigt bei EXPLAIN ANALYZE lediglich den Schreibvorgang ins WAL, jedoch nicht das fsync.
Wäre das denkbar?

Nein. Unabhängig davon, ob fsync mit gemessen wird order nicht, wäre dies auch keine Erklärung für die ursprüngliche Frage. Ich vermute die Ursache eher im Java-Treiber.

Alternativ zum offiziellen JDBC-Treiber von PostgreSQL habe ich auch den Treiber unter [1] ausprobiert.
Der Effekt blieb jedoch identisch.

[1] https://github.com/impossibl/pgjdbc-ng