Performance Tuning - Grundeinstellungen und Joins mit Where

Hallo zusammen,

ich habe mittlerweile meine erste Datenbank in Betrieb :slight_smile:

Anfangs lief alles richtig gut, aber nun, wo sich eine ganze Menge an Daten angesammelt hat, sind bestimmte Abfragen unvorstellbar langsam.
Die Laufzeit liegt im Bereich von mehreren Stunden.

Dies ist sicherlich auf meine Anfängerabfragen und ungeeigente Einstellungen zurückzuführen.

Nun hoffe ich, dass ihr mir bei der Optimierung ein wenig helfen könntet :slight_smile:

Ich habe insgesamt 10 Tabelle, in denen Messwerte verschiedenster Sensoren gespeichert werden.
Einige Tabellen beeinhalten recht wenige Reihen, andere recht viele.
Die größte Tabelle besitzt zzt. 3 Millionen Reihen bei 6 Spalten.
Jeder Datenpunkt in allen Tabellen besitzt einen “Timestamp without time zone”.

Mein Ziel ist es regelmäßig CSV-Dateien für bestimmte Zeitbereiche, die immer unterschiedlich sein können zu exportieren.
Dafür muss ich einige Tabellen transponieren, damit alle Tabellen dasselbe Ausgangsformat haben.
Anschließend nutze ich Joins mit einer Where-Bedingung, um alle 10 Tabellen zu verknüfpen und in eine CSV-Datei zu exportieren.

Wie kann ich die Datenbank und meine Abfrage optimieren?
Die Datenbank läuft komplett mit Standardeinstellungen.

Ist es sinnvoll einen Index auf den Zeitstempel aller Tabellen zu legen?
(Es kommen spätestens alle 200 ms 10 neue Datenpunkte und die Datenbank läuft auf einem 3-Jahre alten Windows-Laptop.)
Die neueren Einträge sind tendenziell die interessanteren.
Wäre dies hier der richtige Weg:

CREATE INDEX CONCURRENTLY  tabelle1_zeit_idx ON tabelle1(zeit DESC NULLS LAST);

Der Index würde dann bei jedem neuen Eintrag automatisch erweitert werden, oder?

Dann habe ich noch gelesen, dass ich “Vacuum” und “Analyze” automatisch und möglichst oft (vielleicht alle 5000 neuen Einträge) laufen lassen sollte.
Ist das so richtig?


Kann man meine Abfrage vielleicht auch optimieren?
Die sieht im Prinzip momentan so aus (Kurzversion):

DROP TABLE IF EXISTS temp_contactor;
CREATE TEMP TABLE temp_contactor
as
SELECT *
FROM(
      SELECT 
         time,
         extract(epoch from t_cabinet_contactor_times.time) * 1000 as "absolute_time",
         bool_and(value) filter (where component = 'light') as light, bool_and(value) filter (where component = 'ventilation') as ventilation, bool_and(value) filter (where component = 'compressor') as compressor
         from t_cabinet_contactor_times group by time order by time 
    ) as temp_contactor;


COPY(

SELECT 

	COALESCE(temp_contactor.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time ) as "time",

	extract(epoch from COALESCE(temp_contactor.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time)) * 1000 as "absolute_time",  --Extracts the absolute value from time in milliseconds!

	temp_contactor.light,
	temp_contactor.ventilation,
	t_cabinet_temperature._10T1,
	t_cabinet_temperature._10T2,
	t_cabinet_relative_humidity._10rh1,
	t_cabinet_relative_humidity._10rh2,
	t_cabinet_kwh.light,
	t_cabinet_kwh.ventilation
	
FROM
(
	temp_contactor FULL OUTER JOIN t_cabinet_temperature ON temp_contactor.time = t_cabinet_temperature.time
	FULL OUTER JOIN t_cabinet_relative_humidity ON t_cabinet_relative_humidity.time = t_cabinet_temperature.time
	FULL OUTER JOIN t_cabinet_kwh ON t_cabinet_kwh.time = t_cabinet_temperature.time
)



WHERE COALESCE(temp_contactor.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time  ) BETWEEN SYMMETRIC '2016-01-31 10:52:00.000' AND '2016-04-16 15:52:59.999'

ORDER BY
	time ASC

	)

TO 'C:\test\export.csv'
WITH DELIMITER ';'
CSV HEADER

Vielen Dank für die Hilfe :slight_smile:

Viele Fragen auf einmal …

Indexe helfen, wenn diese korrekt gesetzt sind. Um das zu prüfen, kann man EXPLAIN benutzen, idealerweise in Verbindung mit ANALYSE. Falls Du mit 9.5+ arbeitest und typische Logtabellen hast (Append only) und Abfragen sich auf Zeit(ranges) beziehen sind BRIN-Indexe ideal. Beachte aber, daß unnötig definierte Indexe Speicher und Performance fressen. Also mit Bedacht Indexe erstellen!

Temporäre Tabellen solltest Du nach Möglichkeit vermeiden, nutze lieber Views, um Abfragen zu vereinfachen. Der Planner sieht dann die Abfrage im Ganzen und kann besser optimieren. Analyse manuell kannst Du nach größeren Änderungen aufrufen, ja. Vacuum manuell aufzurufen ist eher nicht nötig, falls doch, sollte man eher Autovacuum anpassen.

Ich weiß, dass das etwas viele Fragen waren :wink:

Vielen Dank für die super Antwort!
Das hilft mir sehr weiter!

Die Tabellen sind “Append only”-Tabellen - zumindest werden sie so genutzt.
Muss ich bei Postgres irgendwo auch noch angeben, dass es sich um einen solchen Tabellentyp handelt?

Aktualisieren sich die Brin-Indizes automatisch wenn ich neue Daten einfüge?


Das mit den Views ist auch ein guter Hinweis.

Dankeschön :slight_smile:

Nein, muß man nicht extra angeben, daß das nur Append-Only-Tabellen sind, aber BRIN-Indexe arbeiten am besten auf solchen Tabellen. Und ja, sie aktualisieren sich automatisch.

Vielen Dank für die Hilfe :slight_smile:
Ich habe nun die temporären Tabellen durch Sichten ersetzt und BRIN-Indizes eingefügt.
Zumindest bei meiner langsamen Abrage hat sich dadurch leider nichts gebessert.
Die Abfrage braucht fast drei Stunden :frowning:

Es wäre echt prima, wenn ich hier noch ein wenig Hilfe bekommen könnte.

Ich habe nun extra die Statistik dazu erzeugt, weiß aber nicht, wie ich jetzt irgendetwas optimeren kann.

http://explain.depesz.com/s/Tvhe

Das hier ist die originale Abfrage:

EXPLAIN (ANALYZE, BUFFERS)  SELECT 

	COALESCE(v_cabinet_contactor_times_transposed.time, t_cabinet_pema_sensors.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time, t_cabinet_condensate_water.time, v_lund_science_data_transposed.time, v_measured_angles_transposed.time, t_measured_temperatures.time ) as "time",

	extract(epoch from COALESCE(v_cabinet_contactor_times_transposed.time, t_cabinet_pema_sensors.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time, t_cabinet_condensate_water.time, v_lund_science_data_transposed.time, v_measured_angles_transposed.time, t_measured_temperatures.time )) * 1000 as "absolute_time",  --Extracts the absolute value from time in milliseconds!

	v_cabinet_contactor_times_transposed.light,
	v_cabinet_contactor_times_transposed.ventilation,
	v_cabinet_contactor_times_transposed.compressor,
	t_cabinet_pema_sensors.mass_flow,
	t_cabinet_pema_sensors.temperature_gas,
	t_cabinet_pema_sensors.temperature_liquid,
	t_cabinet_pema_sensors.pressure_gas,
	t_cabinet_pema_sensors.pressure_liquid,
	t_cabinet_temperature._10T1,
	t_cabinet_temperature._10T2,
	t_cabinet_temperature._20T1,
	t_cabinet_temperature._20T2,
	t_cabinet_temperature._30T1,
	t_cabinet_temperature._30T2,
	t_cabinet_relative_humidity._10rh1,
	t_cabinet_relative_humidity._10rh2,
	t_cabinet_relative_humidity._20rh1,
	t_cabinet_relative_humidity._20rh2,
	t_cabinet_relative_humidity._30rh1,
	t_cabinet_relative_humidity._30rh2,
	t_cabinet_kwh.light,
	t_cabinet_kwh.ventilation,
	t_cabinet_kwh.compressor,
	t_cabinet_kwh.pump,
	t_cabinet_condensate_water.condensate,
	v_lund_science_data_transposed.light,
	v_lund_science_data_transposed.ventilation,
	v_lund_science_data_transposed.compressor,
	v_lund_science_data_transposed.pump,
	v_lund_science_data_transposed.temp_inside,
	v_lund_science_data_transposed.temp_outside,
	v_lund_science_data_transposed.hum_inside,
	v_lund_science_data_transposed.hum_outside,
	v_measured_angles_transposed.door1,
	v_measured_angles_transposed.door2,
	v_measured_angles_transposed.door3,
	v_measured_angles_transposed.door4,
	v_measured_angles_transposed.door5,
	v_measured_angles_transposed.door6,
	t_measured_temperatures.ch1,
	t_measured_temperatures.ch2,
	t_measured_temperatures.ch3,
	t_measured_temperatures.ch4,
	t_measured_temperatures.ch5,
	t_measured_temperatures.ch6,
	t_measured_temperatures.ch7,
	t_measured_temperatures.ch8,
	t_measured_temperatures.ch9,
	t_measured_temperatures.ch10
	
FROM
(
	v_cabinet_contactor_times_transposed FULL OUTER JOIN t_cabinet_pema_sensors ON v_cabinet_contactor_times_transposed.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN t_cabinet_temperature ON t_cabinet_temperature.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN t_cabinet_relative_humidity ON t_cabinet_relative_humidity.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN t_cabinet_kwh ON t_cabinet_kwh.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN t_cabinet_condensate_water ON t_cabinet_condensate_water.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN v_lund_science_data_transposed ON v_lund_science_data_transposed.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN v_measured_angles_transposed ON v_measured_angles_transposed.time = t_cabinet_pema_sensors.time
	FULL OUTER JOIN t_measured_temperatures ON t_measured_temperatures.time = t_cabinet_pema_sensors.time
)



WHERE COALESCE(v_cabinet_contactor_times_transposed.time, t_cabinet_pema_sensors.time, t_cabinet_temperature.time, t_cabinet_relative_humidity.time, t_cabinet_kwh.time, t_cabinet_condensate_water.time, v_lund_science_data_transposed.time, v_measured_angles_transposed.time, t_measured_temperatures.time  ) BETWEEN SYMMETRIC '2016-04-04 10:52:00.000' AND '2016-04-16 15:52:59.999'

ORDER BY
	time ASC;

Vielen Dank :slight_smile:

Hoi,

Also mein erster Vorschlag wäre hier den work_mem und die shared_buffers mal ordentlich zu erhöhen (ggf. erstmal nur zum testen in der Session).
Hauptsächlich wegen: Sort Method: external merge Disk: 37224kB und du hast eher wenig shared hits (kann natürlich auch daran liegen, dass sich die Daten sehr häufig ändern. Kann ich so nicht beantworten :slight_smile:).
Du machst hier nen full outer join über ne ganze Menge Tabellen mit anscheinend auch ordentlich Inhalt ( rows=4,893,611). Muss das ein FULL OUTER JOIN sein? Also brauchst du hier auch die NULL-Werte, oder reicht dir ein INNER JOIN (Also nur joinen, wenn auch auf beiden Seiten was vorhanden ist).

Duch die Erhöhung des work_mem sollte dann auch das Sort entsprechend schneller funktionieren. Wobei natürlich dein Index hier nur eher wenig bringt, wenn hier jedesmal andere Werte beim Coalesce drin stehen. Mir ist zumindest spontan nix von einem Tabellenübergreifenden Index bekannt.

Schau mal wie es nach der Speichererhöhung (ruhig ordentlich was dazu geben :wink:) aussieht. Hier musst du sowieso deinen Optimalwert durch testen rausbekommen. Ist ja nicht so, dass du 2GB work_mem machst und alles ist gut, wäre ja auch Verschwendung. Einfach mal bisschen mit den Werten spielen und dir jeweils das Explain anschauen. Fang ruhig bei 1G work_mem an und arbeite dich dann vor und/oder zurück.

ich würde mal versuchen, das Where auf die einzelnen Tabellen aufzudröseln, um insbesondere Step 3 zu beschleunigen. Durch deine Where-Condition können hier so erst einmal keine Indexe wirklich greifen. work_mem reichen vermutlich 64MB aus, um den Sort zu beschleunigen. hared Buffers, wie schon gesagt, höher setzen.

Vielen vielen Dank :slight_smile:

Durch die Erhöhung des working_mem auf 100 MB läuft das ganze jetzt in 30s anstatt in fast drei Stunden :slight_smile:
Ich habe auch mit dem Parameter shared_buffers gespielt, aber der scheint irgendwie keinerlei Auswirkungen zu haben.

Einen Full-Outer-Join brauche ich tatsächlich, weil ich das ganze ja hinterher als CSV exportiere und da brauche ich auch die Null-Werte.


Die “Where”-Klauseln aufzuteilen ist auch noch eine gute Idee.
Das Problem dabei ist, dass ich dann jedes mal für eine Abfrage an vielen Stellen den zu filternden Zeitstempel anpassen müsste.
Ich bräuchte ja sowas wie eine Variable um dann z.B. oben im File einen Zeitstempel zentral zu definieren, der dann überall eingesetzt wird.
Das kann SQL so nicht, oder?

Nein, entweder macht das die Applikation, oder Du baust Dir eine stored Proc.

Das work_mem so gut wirkt ist fein, vermutlich ändert das noch mehr im Plan (nicht nur den Sort). Hast Du das mal untersucht?

Für den Export habe ich eigentlich keine extra Applikation vorgesehen, aber vielleicht schreibe ich noch eine.

Ich habe gerade ein bisschen gegoogelt.
Vielleicht schaue ich mir mal PL/pgSQL an.

Hier ist die neue Analyse:
http://explain.depesz.com/s/GbnO

So wie ich das sehe, hat sich echt nur der Sort geändert.

Ich bin jedenfalls total happy, dass alles so schön funktioniert :slight_smile:

Vielen Dank nochmals!

Hoi,

schön das dass ganze so gut funktioniert. Das Sort war hier auch das härteste, weil er alles auf die Platte auslagern musste. Jetzt kann die DB das im RAM lösen und ist damit natürlich um ein vielfaches schneller. Shared_buffer hatte ich ja schon gesagt, wäre nur ein Versuch, weil du recht wenig Hits im Buffer hattest. Kommt halt drauf an, wie oft sich die Daten ändern und wie oft die Query läuft :wink: