Am 19.02.22 um 15:21 schrieb Erik Schanze:
Am 19.02.22 um 14:52 schrieb Sebastian Weckend:
Deswegen dauert es wahrscheinlich so lang, da ohne Index für die
Sortierung alle Zeilen angeschaut werden müssen. Entweder solltest du
für den Timestamp einen Index hinzufügen (was den aber unique machen
dürfte, also u.U. nicht gewollt) oder einen primary key hinzufügen,
nach dem du sortieren kannst.
Sorry, da bin ich schon raus. ;-)
Der Anwendungsfall hier ist ein Logger, der alle 2 Minuten
Stromzählerwerte in die Datenbank schreibt. Zum Zugriff auf die
Tabellenwerte wird immer der Timestamp genutzt, also alle Werte eines
bestimmten Tages oder eines Monats etc. aus der Datenbank geholt und
dann in PHP in Diagrammen dargestellt.
Wie hilft mir da ein Index? Weitere Spalten wollte ich vermeiden, um die
Datenmenge nicht unnötig aufzublähen.
Wenn Du alle Werte eines Tages holen willst, hilft ein Index schon mal
ganz gewaltig. Ich hab das mal nachgestellt.
PostgreSQL 14.1 in einer lokalen Minikube-Kubernetes-Instanz, daher
nicht unbedingt schnell.
test=# create table logger(created timestamp, c180 bigint, c280 bigint);
CREATE TABLE
test=# insert into logger select '2000-01-01'::timestamp + s *
'1minute'::interval, random()*100000, random()*100000 from
generate_series(1,10000000) s;
INSERT 0 10000000
test=# select min(created), max(created) from logger;
min | max
---------------------+---------------------
2000-01-01 00:01:00 | 2019-01-05 10:40:00
(1 row)
Das sind also 10.000.000 Datenpunkte, minütlicher Abstand, nach ca. 9
Jahren.
test=# explain analyse select created, c180, c180 - lag(c180) over
(order by created), c280, c280 - lag(c280) over (order by created) from
logger;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=1736550.18..1986553.06 rows=10000115 width=40)
(actual time=88513.167..225937.881 rows=10000000 loops=1)
-> Sort (cost=1736550.18..1761550.47 rows=10000115 width=24)
(actual time=88513.112..131629.556 rows=10000000 loops=1)
Sort Key: created
Sort Method: external merge Disk: 332768kB
-> Seq Scan on logger (cost=0.00..163696.15 rows=10000115
width=24) (actual time=0.132..43941.856 rows=10000000 loops=1)
Planning Time: 0.818 ms
Execution Time: 267222.240 ms
(7 rows)
Man sieht, es dauert. Und: es muß zum Sortieren temp. auf Platte
schreiben (Sort Method: external merge Disk: 332768kB).
Jetzt mit Index:
test=# create index idx_logger on logger(created);
CREATE INDEX
test=# explain analyse select created, c180, c180 - lag(c180) over
(order by created), c280, c280 - lag(c280) over (order by created) from
logger;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.43..548386.44 rows=10000000 width=40) (actual
time=0.302..142101.995 rows=10000000 loops=1)
-> Index Scan using idx_logger on logger (cost=0.43..323386.43
rows=10000000 width=24) (actual time=0.270..45946.164 rows=10000000 loops=1)
Planning Time: 0.646 ms
Execution Time: 183889.371 ms
(4 rows)
Das ist schon deutlich schneller.
Ich erstelle mal noch einen anderen Index, der die 2 Spalten als INCLUDE
hat (PostgreSQL-Feature):
test=# create index idx_logger_include on logger(created) include
(c180,c280);
CREATE INDEX
Nun frage ich mal die Daten nur für einen Tag ab:
test=# explain analyse select created, c180, c180 - lag(c180) over
(order by created), c280, c280 - lag(c280) over (order by created) from
logger where created between '2007-07-28'::date and '2007-07-29'::date;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.43..86.96 rows=1377 width=40) (actual
time=0.216..24.339 rows=1441 loops=1)
-> Index Only Scan using idx_logger_include on logger
(cost=0.43..55.97 rows=1377 width=24) (actual time=0.187..8.642
rows=1441 loops=1)
Index Cond: ((created >= '2007-07-28'::date) AND (created <=
'2007-07-29'::date))
Heap Fetches: 0
Planning Time: 0.160 ms
Execution Time: 31.503 ms
(6 rows)
Das dürfte für Dich realistischer sein. Nun lösche ich beide Indexe und
führe letztere Abfrage noch mal aus:
test=# drop index idx_logger;
DROP INDEX
test=# drop index idx_logger_include
test-# ;
DROP INDEX
test=#
test=#
test=# explain analyse select created, c180, c180 - lag(c180) over
(order by created), c280, c280 - lag(c280) over (order by created) from
logger where created between '2007-07-28'::date and '2007-07-29'::date;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=127220.61..127411.97 rows=1377 width=40) (actual
time=336.972..356.114 rows=1441 loops=1)
-> Gather Merge (cost=127220.61..127380.98 rows=1377 width=24)
(actual time=336.853..343.285 rows=1441 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=126220.58..126222.02 rows=574 width=24)
(actual time=330.211..332.998 rows=480 loops=3)
Sort Key: created
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 161kB
-> Parallel Seq Scan on logger (cost=0.00..126194.28
rows=574 width=24) (actual time=261.502..328.286 rows=480 loops=3)
Filter: ((created >= '2007-07-28'::date) AND
(created <= '2007-07-29'::date))
Rows Removed by Filter: 3332853
Planning Time: 0.106 ms
Execution Time: 361.985 ms
(14 rows)
Du siehst, ohne Index ist die Abfrage Faktor 10 langsamer. Was hast Du
an Mengen von Datensätzen, daß das bei Dir so lange dauert?
(PostgreSQL führt die Abfrage auf 3 Cores aus, der Hauptprozess startet
2 Worker-Prozesse)
Btw: was mir grad auffällt, bei den gezeigten Lösungen bisher wird die
Differenz der Zeitspalte berechnet, die aber eigentlich immer 2 Minuten
sein sollte, nach Deinen Ausführungen.
Ich habe die Differenz der Meßwerte zur vorherigen Messung berechnet,
was ist richtig?
Andreas
--
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com
