Hallo zusammen,
ich habe mittlerweile meine erste Datenbank in Betrieb
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
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