Codeoptiierung

Fragen und Probleme rund um die Abfragespache SQL.
mwapost
Neuer Benutzer
Beiträge: 7
Registriert: Sa 5. Okt 2019, 20:55

Codeoptiierung

Beitrag von mwapost » Sa 5. Okt 2019, 21: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:
1. Suche alle Werte im angegebenen Zeitbereich.
2. Zerteile den Zeitbereich in 1500 Werte (konstante Abstände)
3. 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:

Code: Alles auswählen

-- 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:

Code: Alles auswählen

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:

Code: Alles auswählen

stromzaehler.zeit::timestamp

usw verbinden.

Danke schon mal im voraus

akretschmer
Erfahrener Benutzer
Beiträge: 7645
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Codeoptiierung

Beitrag von akretschmer » So 6. Okt 2019, 00:06

welcher PG-Version, was sagt der Abfrageplan?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

mwapost
Neuer Benutzer
Beiträge: 7
Registriert: Sa 5. Okt 2019, 20:55

Re: Codeoptiierung

Beitrag von mwapost » So 6. Okt 2019, 00:21

Version ist 9.3.20

Der Abfrageplan bringt

Code: Alles auswählen

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
Neuer Benutzer
Beiträge: 7
Registriert: Sa 5. Okt 2019, 20:55

Re: Codeoptiierung

Beitrag von mwapost » So 6. Okt 2019, 11:54

Hi,

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

Code: Alles auswählen

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:

Code: Alles auswählen

"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
Erfahrener Benutzer
Beiträge: 1150
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Codeoptiierung

Beitrag von castorp » So 6. Okt 2019, 12:49

Diesen Ausdruck:

Code: Alles auswählen

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:

Code: Alles auswählen

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

Code: Alles auswählen

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;
Zuletzt geändert von castorp am So 6. Okt 2019, 13:06, insgesamt 3-mal geändert.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

akretschmer
Erfahrener Benutzer
Beiträge: 7645
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Codeoptiierung

Beitrag von akretschmer » So 6. Okt 2019, 12: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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

mwapost
Neuer Benutzer
Beiträge: 7
Registriert: Sa 5. Okt 2019, 20:55

Re: Codeoptiierung

Beitrag von mwapost » So 6. Okt 2019, 17:58

Hi,

ich hab mir mal eure Tips angeschaut:

Also nach Abänderung des Codes zu dieser Funktion:

Code: Alles auswählen


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:

Code: Alles auswählen

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
Erfahrener Benutzer
Beiträge: 1150
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Codeoptiierung

Beitrag von castorp » So 6. Okt 2019, 18:59

Das hier:

Code: Alles auswählen

(SELECT ( extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp) ) /1500)
ist absolut das identisch zu:

Code: Alles auswählen

extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500
Also kann man das obige vereinfachen zu:

Code: Alles auswählen

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

Code: Alles auswählen

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

Code: Alles auswählen

make_interval(secs => extract(epoch from '2019-10-03'::timestamp - '2019-9-01'::timestamp)/1500)
vereinfachen (make_interval gibt's seit 9.4)
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

pogomips
Erfahrener Benutzer
Beiträge: 110
Registriert: Fr 18. Nov 2016, 09:55

Re: Codeoptiierung

Beitrag von pogomips » Mi 9. Okt 2019, 22:51

mwapost hat geschrieben:
Sa 5. Okt 2019, 21:09
.. welche mir aus einer großen Tabelle mit Datumsstempel und Daten folgendes macht:
1. Suche alle Werte im angegebenen Zeitbereich.
2. Zerteile den Zeitbereich in 1500 Werte (konstante Abstände)
3. Suche nach allen Werte in jedem Zeitbereich und führe einen Mittelwert und die Standardabweichung durch
Als ich das gelesen hab, dachte ich zuerst, das müssten Windows Functions sein, aber das ist es nicht mal wirklich.
Außerdem dachte ich, das ist der erste, der Sensordaten sinnvoll (!) aggregieren möchte, bevor er sie in ein Dashboard quetscht. Nämlich auf eine fixe Anzahl von Punkten, vermutlich analog zu einer realistischen und maximalen Auflösung der Grafik(karte) - physikalisch oder bildlich sollte hier egal sein.

Naja vielleicht hab ich Dich auch falsch verstanden, jedenfalls habe ich mir sowas überlegt:
1. Man definiert ein Merkmal, das mittels Gruppierung die gewünschte Auflösung ergibt, hier 1500. Da die Ausgangsmenge naturgemäß und durch willkürliche Datumseingrenzung schwankt, muss man hier etwas rumrechnen.
Der 2. Punkt wäre die Vermeidung einer Funktion. Das ist in SQL "immer" schlecht, sofern diese wieder Daten (aus Tabellen) selektiert und damit nicht deterministisch ist. Das geht hier am einfachsten, wenn man die Parameter und abhängige Werte als eigenständiges Select baut. Durch die Datumseinschränkung muss man in der Form an 2 Stellen eine synchrone Where Clause konstruieren. Das ist natürlich nicht so elegant wie eine Funktion, aber es ist hoffentlich durch den Performancegewinn zu verschmerzen*.
In der Form unten sollten es jedenfalls max 2 Fullscan bzw. mit kleinerem Datumsrange entsprechend weniger sein. Könnte auch auf einem PI gut laufen.

Hier läuft es auf dem Laptop mit 1,5 Mio rows in 300 ms (first run)

Falls nicht, ist das Prinzip jedenfalls vielseitig ;)

Code: Alles auswählen

select
	r,                                         -- als sone art Aggregat ID 
	min(zeit) as z,                      -- AVG auf Zeit ist schwierig, also min oder max
	avg("bezug(kwh)") as b,        -- irgendwas Aggregiertes, Standardabweichung usw. geht sicher auch
	avg("pv(kwh)") as p,
	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 now() and NOW() + interval '16h')s,
		   (select (count(*)/1500)+1  as Param from stromzaehler s where s.zeit between now() and NOW() + interval '16h') 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
* mit CTE unter Version 12 wäre die where Clause nur einmal notwendig und es würde nicht materialisiert. Da in diesem Fall hier unabhängig von der Version das Ergebnis beider Selects recht übersichtlich ist (max 1500 und 1 Datensätze) sollte das aber nicht so problematisch sein.

mwapost
Neuer Benutzer
Beiträge: 7
Registriert: Sa 5. Okt 2019, 20:55

Re: Codeoptiierung

Beitrag von mwapost » So 13. Okt 2019, 18:33

Hallo,

also zum Vergleich:

Code:

Code: Alles auswählen


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:

Code: Alles auswählen

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

Antworten