Codeoptiierung

mwapost · 5. Oktober 2019 um 19:09

Hallo liebe Gemeinde,

ich bin neu im Forum und habe ein Frage bezüglich der Codeoptimierung in Postgres. Ich habe eine Funktion dort geschrieben, welche mir aus einer großen Tabelle mit Datumsstempel und Daten folgendes macht:

Suche alle Werte im angegebenen Zeitbereich.
Zerteile den Zeitbereich in 1500 Werte (konstante Abstände)
Suche nach allen Werte in jedem Zeitbereich und führe einen Mittelwert und die Standardabweichung durch

Geben das Ergebnis der Liste aus.

An und für sich funktioniert der Code, allerdings ist meine Ursprungstabelle so groß, dass eine Abfrage schon mal bis zu 30 Sekunden und länger dauern kann. Von daher wollte ich mal Fragen, ob ihr Optimierungsvorschläge dazu habt?

Die Tabelle stromzaehler gilt als Beispiel und hat folgende Spalten:

-- Table: stromzaehler

-- DROP TABLE stromzaehler;

CREATE TABLE stromzaehler
(
  zeit timestamp without time zone NOT NULL,
  "bezug(kwh)" double precision NOT NULL, -- Energiebezug vom Anbieter in kWh
  "pv(kwh)" double precision NOT NULL, -- Energiebezug zum Anbieter in kWh
  leistung double precision NOT NULL -- Aktuelle Leistung in W (-:zum Anbieter, +:vom Anbieter)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stromzaehler
  OWNER TO pi;
COMMENT ON COLUMN stromzaehler."bezug(kwh)" IS 'Energiebezug vom Anbieter in kWh';
COMMENT ON COLUMN stromzaehler."pv(kwh)" IS 'Energiebezug zum Anbieter in kWh';
COMMENT ON COLUMN stromzaehler.leistung IS 'Aktuelle Leistung in W (-:zum Anbieter, +:vom Anbieter)';


-- Index: index_zeit

-- DROP INDEX index_zeit;

CREATE INDEX index_zeit
  ON stromzaehler
  USING btree
  (zeit);

Hier der aktuelle Code:

CREATE OR REPLACE FUNCTION getgraph(
    IN t1 text,
    IN t2 text)
  RETURNS TABLE(datum timestamp without time zone, avg1 double precision, sigma double precision) AS
$BODY$
SELECT t1::timestamp + T * ((SELECT ( DATE_PART('day', t2::timestamp - t1::timestamp) * 24*60*60 + 
                DATE_PART('hour', t2::timestamp - t1::timestamp) * 60*60 +
                DATE_PART('minute', t2::timestamp - t1::timestamp) * 60 +
                DATE_PART('second', t2::timestamp - t1::timestamp) ) /1500) || ' seconds')::interval, Q.power, Q.sigma FROM 
  
  (SELECT round(( DATE_PART('day', stromzaehler.zeit::timestamp - t1::timestamp) * 24*60*60 + 
                DATE_PART('hour', stromzaehler.zeit::timestamp - t1::timestamp) * 60*60 +
                DATE_PART('minute', stromzaehler.zeit::timestamp - t1::timestamp) * 60 +
                DATE_PART('second', stromzaehler.zeit::timestamp - t1::timestamp) )/(SELECT ( DATE_PART('day', t2::timestamp - t1::timestamp) * 24*60*60 + 
                DATE_PART('hour', t2::timestamp - t1::timestamp) * 60*60 +
                DATE_PART('minute', t2::timestamp - t1::timestamp) * 60 +
                DATE_PART('second', t2::timestamp - t1::timestamp) ) /1500)) AS T, avg(stromzaehler.leistung) as power, stddev(stromzaehler.leistung) as sigma FROM stromzaehler WHERE stromzaehler.zeit::timestamp between t1::timestamp and t2::timestamp group by T ORDER BY T) AS Q;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION getgraph(text, text)
  OWNER TO pi;
COMMENT ON FUNCTION getgraph(text, text) IS 'Die Funktion zerteilt alle Zeilen in der Tabelle stromzähler in maximal 1500 Teilstücke und verteilt die Spalten (mittels der Mittelwerte des Zeitintervals) in diese Intervalle. 

SELECT getgraph('2019-09-01', '2019-09-03');';

Ach und ich hätte noch eine zweite Frage dazu:
Wie kann man der Funktion zum Einen die Tabelle stromzaehler und zum Anderen die zu nutzende Wertespalte dynamisch übergeben. Ich konnte das nicht mit dem code:

stromzaehler.zeit::timestamp

usw verbinden.

Danke schon mal im voraus

akretschmer · 5. Oktober 2019 um 22:06

welcher PG-Version, was sagt der Abfrageplan?

mwapost · 5. Oktober 2019 um 22:21

Version ist 9.3.20

Der Abfrageplan bringt

Subquery Scan on echo  (cost=11624.900..11625.080 rows=2 width=24) (actual time=13800.117..13817.010 rows=1478 loops=1)
    Buffers: shared hit=14493
  InitPlan
    ->  Result  (cost=0.000..0.010 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
  InitPlan
    ->  HashAggregate  (cost=11624.890..11625.020 rows=2 width=16) (actual time=13800.052..13802.359 rows=1478 loops=1)
            Buffers: shared hit=14493
          InitPlan
            ->  Result  (cost=0.000..0.010 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
          InitPlan
            ->  Bitmap Heap Scan on mike  (cost=168.840..11566.040 rows=7845 width=16) (actual time=582.692..11285.087 rows=1436735 loops=1)
                    Recheck Cond: ((five >= 'six'::timestamp without time zone) AND (five <= 'juliet'::timestamp without time zone))
                    Buffers: shared hit=14493
                  ->  Bitmap Index Scan on delta  (cost=0.000..166.880 rows=7845 width=0) (actual time=582.411..582.411 rows=1436735 loops=1)
                          Index Cond: ((five >= 'six'::timestamp without time zone) AND (five <= 'juliet'::timestamp without time zone))
                          Buffers: shared hit=3928

zum Vorschein.

Es hat also irgendwas mit dem between zu tun. Ich hatte mich damit die letzten paar Stunden auseinandergesetzt. Also Antwort für mich kam im wesentlichen heraus, dass man ggf. den Timestamp kopieren sollte und dann ein logisches UND erzeugt, anstatt ein Between zu nutzen. Die Parallelnutzung von Postgres 9.6 kann ich leider derzeit nicht nutzen.

Was mir auch noch aufgefallen ist:
Wenn ich die Funktion ausführe braucht die Datenbank knapp 30 Sekunden für die Abfrage. Wenn ich aber den Code nehme und t1 und t2 direkt mit dem String besetze und das als Abfrage wegschicke, dann dauert die Abfrage nur 13 Sekunden. Kann es sein, dass es irgenwelche Cast Probleme gibt?

mwapost · 6. Oktober 2019 um 09:54

Hi,

ich hab den Code nochmal ein wenig umgeschrieben, besser wurde es allerdings nicht, dafür ist der Code übersichtlicher:

WITH Q AS
(SELECT round(( DATE_PART('day', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 24*60*60 + 
                DATE_PART('hour', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 60*60 +
                DATE_PART('minute', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 60 +
                DATE_PART('second', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) )/(SELECT ( DATE_PART('day', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 24*60*60 + 
                DATE_PART('hour', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 60*60 +
                DATE_PART('minute', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 60 +
                DATE_PART('second', '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500)) AS T,
                avg(stromzaehler.leistung) as power, stddev(stromzaehler.leistung) as sigma FROM stromzaehler WHERE stromzaehler.zeit::timestamp between '2019-9-01'::timestamp and '2019-10-03'::timestamp group by T)


SELECT '2019-9-01'::timestamp + Q.T * ((SELECT ( DATE_PART('day', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 24*60*60 + 
                DATE_PART('hour', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 60*60 +
                DATE_PART('minute', '2019-10-03'::timestamp - '2019-9-01'::timestamp) * 60 +
                DATE_PART('second', '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500) || ' seconds')::interval as time, Q.power, Q.sigma FROM Q

Der Abfrageplan lautet:

"CTE Scan on q  (cost=11800.17..11800.24 rows=2 width=24) (actual time=12351.290..12370.630 rows=1478 loops=1)"
"  CTE q"
"    ->  HashAggregate  (cost=11800.03..11800.16 rows=2 width=16) (actual time=12351.240..12353.595 rows=1478 loops=1)"
"          InitPlan 1 (returns $0)"
"            ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"          ->  Bitmap Heap Scan on stromzaehler  (cost=170.06..11740.29 rows=7964 width=16) (actual time=450.112..10072.785 rows=1436735 loops=1)"
"                Recheck Cond: ((zeit >= '2019-09-01 00:00:00'::timestamp without time zone) AND (zeit <= '2019-10-03 00:00:00'::timestamp without time zone))"
"                ->  Bitmap Index Scan on index_zeit  (cost=0.00..168.07 rows=7964 width=0) (actual time=444.896..444.896 rows=1436735 loops=1)"
"                      Index Cond: ((zeit >= '2019-09-01 00:00:00'::timestamp without time zone) AND (zeit <= '2019-10-03 00:00:00'::timestamp without time zone))"
"  InitPlan 3 (returns $2)"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)"
"Total runtime: 12371.244 ms"

castorp · 6. Oktober 2019 um 10:49

Diesen Ausdruck:

DATE_PART('day', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 24*60*60 + 
 DATE_PART('hour', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 60*60 +
DATE_PART('minute', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp) * 60 +
DATE_PART('second', stromzaehler.zeit::timestamp - '2019-9-01'::timestamp)

kannst Du vereinfachen.

Die Anzahl von Sekunden zwischen zwei Timestamps lässt sich einfach durch subtrahieren der beiden Timestamps ermitteln. Dabei wird ein interval zurückgegeben und extract(epoch from …) liefert die Gesamtanzahl der Sekunden in dem Interval:

extract(epoch from zeit::timestamp - '2019-9-01'::timestamp)

Allerdings wird das die Sache vermutlich nicht schneller machen.

Und der Ausdruck braucht auch kein weiteres SELECT davor. Und es ist auch sinnvoll der Funktion direkt mit timestamp Parametern zu definieren.
Die Spalte ZEIT ist bereits ein Timestamp, da muss man auch nichts mehr casten

CREATE OR REPLACE FUNCTION getgraph(IN t1 timestamp, IN t2 timestamp)
  RETURNS TABLE(datum timestamp without time zone, avg1 double precision, sigma double precision) AS
$BODY$
SELECT t1 + T * extract(epoch from t2 - t1), Q.power, Q.sigma 
FROM (
  SELECT round( extract(epoch from zeit - t1) / extract(epoch from t2 - t1)) /1500)) AS T, 
         avg(leistung) as power, 
         stddev(leistung) as sigma 
  FROM stromzaehler 
  WHERE zeit between t1 and t2
  group by T
) AS Q
$BODY$
  LANGUAGE sql 
  VOLATILE
  COST 100
  ROWS 1000;

akretschmer · 6. Oktober 2019 um 10:55

einige Dinge, die auffallen:

veraltete Version. 9.3 ist seit mehr als 1 Jahr out of support, dazu kommt, daß du auch nicht die letzte Minor-Version nutzt
massive Verschätzung des Planners: Bitmap Index Scan on index_zeit, rows=7964, actual rows=1436735
autovacuum und autoanalyse arbeiten auf der Tabelle?
teuer ist der Bitmap Heap Scan. Seit 9.2 können wir index-only-scans. Ein Index über zeit und leistung (die anderen Spalten fragst ja nicht ab) könnte helfen. Dazu muß aber (AUTO)VACUUM auf der Tabelle laufen, damit die visibility-map aktuell ist. Ich vermute, daß das eine Append-Only-Tabelle ist, daher evtl. per CRON oder so regelmäßig ein VACUUM laufen lassen, weil Autovac die Tabelle sonst möglicherweise ‘übersieht’. Kannst Du in pg_stat_user_tables prüfen.
Vorsicht bei CTE-Abfragen, die werden erst ab Version 12 richtig optimiert, bis dahin immer materialisiert
dein zuerst gezeigtes Explain paßt nicht zur Tabelle & Abfrage

Wie viele Zeilen hat die Tabelle insgesamt? Wenn die Tabelle nicht wesentlich mehr als die 1.4 Millionn Rows hat, wäre ein seq-scan eher besser.
Falls dem so ist: set enable_bitmapscan to off; Wenn er dann einen normalen Indexscan macht auch noch set enable_indexscan to off; und schauen,
ob ein seq-scan schneller geht. Hier wäre dann die parallele Ausführung solcher Scans ab 9.6 natürlich hilfreich. Mit anderen Worten: Upgrade planen.

mwapost · 6. Oktober 2019 um 15:58

Hi,

ich hab mir mal eure Tips angeschaut:

Also nach Abänderung des Codes zu dieser Funktion:

WITH Q AS
(SELECT round(( extract(epoch from stromzaehler.zeit::timestamp - t1::timestamp) )/(SELECT ( extract(epoch from t2::timestamp - t1::timestamp) ) /1500)) AS T,
                avg(stromzaehler.leistung) as power, stddev(stromzaehler.leistung) as sigma FROM stromzaehler WHERE stromzaehler.zeit::timestamp between t1::timestamp and t2::timestamp group by T)


SELECT t1::timestamp + Q.T * ((SELECT ( extract(epoch from t2::timestamp - t1::timestamp) ) /1500) || ' seconds')::interval as time, Q.power, Q.sigma FROM Q ORDER BY time

hat sich die Geschwindigkeit im Abarbeiten verdoppelt. Hat also enorm viel gebracht.
Die SELECTS konnte ich nicht löschen, das hat mir dann nur noch eine Zeile in der Ausgabe beschert. Ich habe aber, um ehrlich zu sein, nicht wirklich darüber nachgedacht, was das Fehlen der Select ausmacht!?

Ein Vacuum der Tabelle hat nichts bewirkt
genauso wie das Abschalten von enable_bitmapscan und enable_indexscan

Unabhängig davon verstehe ich noch nicht so ganz, warum ich nochmals einen Faktor 2 gewinne, wenn ich die Abfrage ohne Funktion ausführe? Wisst ihr da mehr? Also folgenden Code ausführe:

WITH Q AS
(SELECT round(( extract(epoch from stromzaehler.zeit - '2019-9-01'::timestamp) )/(SELECT ( extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500)) AS T,
                avg(stromzaehler.leistung) as power, stddev(stromzaehler.leistung) as sigma FROM stromzaehler WHERE stromzaehler.zeit between '2019-9-01'::timestamp and '2019-10-03'::timestamp group by T)


SELECT '2019-9-01'::timestamp + Q.T * ((SELECT ( extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500) || ' seconds')::interval as time, Q.power, Q.sigma FROM Q ORDER BY time

Ach und ich hab vergessen ein paar Fragen zu Beantworten…

Also derzeit hat die Tabelle 1,4 Mio Einträge. Diese sammeln sich in etwa pro Monat. Das heißt in einem Jahr sind es knapp 10x soviele. usw.

castorp · 6. Oktober 2019 um 16:59

Das hier:

(SELECT ( extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500)

ist absolut das identisch zu:

extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500

Also kann man das obige vereinfachen zu:

WITH Q AS (
  SELECT round( extract(epoch from zeit - '2019-9-01'::timestamp) / (extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500) ) AS T,
         avg(leistung) as power, 
         stddev(leistung) as sigma 
  FROM stromzaehler 
  WHERE zeit between '2019-9-01'::timestamp and '2019-10-03'::timestamp 
  group by T
)
SELECT '2019-9-01'::timestamp + Q.T * (interval '1 second' * (extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500)) as time, 
       Q.power, 
       Q.sigma 
FROM Q 
ORDER BY time

Würdest Du eine unterstützte Postgres Version verwenden, dann könnte man

(interval '1 second' * (extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500))

zu

make_interval(secs => extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500)

vereinfachen (make_interval gibt’s seit 9.4)

mwapost · 13. Oktober 2019 um 16:33

Hallo,

also zum Vergleich:

Code:

WITH Q AS
(SELECT round(( extract(epoch from stromzaehler.zeit::timestamp - '2019-09-01'::timestamp) )/(extract(epoch from '2019-10-13'::timestamp - '2019-09-01'::timestamp) /1500)) AS T,
                avg(stromzaehler.leistung) as power, stddev(stromzaehler.leistung) as sigma FROM stromzaehler WHERE stromzaehler.zeit::timestamp between '2019-09-01'::timestamp and '2019-10-13'::timestamp group by T)


SELECT '2019-09-01'::timestamp + Q.T * ((SELECT ( extract(epoch from '2019-10-13'::timestamp - '2019-09-01'::timestamp) ) /1500) || ' seconds')::interval as time, Q.power, Q.sigma FROM Q ORDER BY time

benötigt aktuell: 6.8 Sekunden

Code:

select
	r,                                         -- als sone art Aggregat ID 
	min(zeit) as z,                      -- AVG auf Zeit ist schwierig, also min oder max
	avg(leistung) as l
  from (select
			row_number() over (                             -- Merkmal für Aggregat erzeugen
			order by zeit )/ param r,                          
			s.*
		from
		   (select * from stromzaehler s where s.zeit between '2019-09-01'::timestamp and '2019-10-13'::timestamp)s,
		   (select (count(*)/1500)+1  as Param from stromzaehler s where s.zeit between '2019-09-01'::timestamp and '2019-10-13'::timestamp) p) x
		   -- Typ und Exaktheit von Param muss man nach Bedarf wählen, hier Ganzzahl (also gerundet) plus 1, 
		   -- also bleibt man immer (knapp) unter oder gleich 1500 Ergebnissätzen,   
		   --      (zumindest wenn die eingeschränkte Menge nicht sehr nahe an 1500 liegt.)
		
		   -- x sollte durch den Index auf dem Filter und durch Ergebnismenge 1 relativ schmerzfrei/schnell zu haben sein, 
		   --      auch auf kleinen Systemen
group by r
order by r

benötigt 13,6 Sekunden

Ich habe im übrigen auf meiner NAS übe rein Docker Image jetzt eine aktuelle Postgres installiert und zum Testen die Daten der alten Datenbank migriert. Die Zeiten für den Code werden dort zumindest nicht schneller. (Zumindest für den oben gezeigten Code). Ich muss mir mal wieder ein wenig mehr Zeit freischaufeln um da genauer zu schauen.

Danke erstmal

mwapost · 13. Oktober 2019 um 17:07

Hi,

mir ist im übrigen noch etwas aufgefallen:
Ich dachte mir ich könnte der Spalte Zeit auch einen Constraint mitgeben, dass diese Spalte eindeutig sei. Meine Erwartung war, dass dann alles schneller wird. Allerdings war das nicht so. Mein Code von vorher 6.8 Sekunde verdoppelte sich auf 13,6 Sekunden. Dafür verkürzte sich der Code von pogomips von 13,6s auf 12,0 Sekunden.

Wie kann man Postgres beibringen, in einer geordneten Tabelle zu suchen. Dass sollte ja innerhalb eines Betweens die Geschwindigkeit enorm verbessern, oder? Ich dachte da an sowas wie ein Constraint und eine Art Index, welcher von einer sortierten Tabelle ausgeht?

pogomips · 13. Oktober 2019 um 18:12

Schade, das hatte ich mir irgendwie anders vorstellt.

zu den beiden Codevarianten:
Ich habe Dich so verstanden, dass die WITH Variante von Dir innerhalb einer Funktion eingesetzt wird/ werden soll.
Meine Variante soll das jedenfalls nicht. Ich gehe davon aus, die Zeitangaben stammen von den Laufzeiten genau dieser Statements.

Dann sieht man, dass die Laufzeiten mit meinem Statement sich grob verdoppeln.
Die With Clause Variante schafft tatsächlich eine Single Pass Lauf, meine Variante durch das Count(*) braucht 2 scans, doppelt so lange. Also eigentlich alles erwartbar.

Wenn Du bei einem Single Pass “angekommen” bist, muss das Tuning schon sehr filigran werden. Viel Aufwand, wenig Verbesserungspotential.
Das läuft dann vielleicht eher darauf hinaus, ein “Anwendungstuning” zu machen oder die Postgres Parameter an die Raspi Umgebung / Aufgabenstellung zu optimieren.

Zu Deinem letzten Post, Sortierung will man nicht, wenn vermeidbar, sie kostet extra Ressourcen, die gerade auf dem RASPI knapp sind.

mwapost · 13. Oktober 2019 um 18:17

Mhh zum Thema Sortierung,

ich denke die größte Ressource welche die Abfrage frisst sitzt in dem Group by in Verbindung mit der Where Klausel. Bevor gruppiert wird muss jedesmal die ganze Tabelle nach allen Möglichkeiten durchsucht werden. Das ist natürlich aufwendig. Wenn man aber bedenkt, dass bei einer sortierten Tabelle die werte direkt hintereinander stehen, dann sollte das Gruppieren doch sehr schnell von der Hand gehen, oder? Aber vielleicht macht das ja Postgres bereits im Hintergrund.