Ich habe Performanceschwierigkeiten bei einer eigentlich recht primitiven Abfrage.
Die Einträge meines tables haben alle einen Start- und Endzeitpunkt mit Namen timestampbegin und timestampend, jeweils als Typ TIMESTAMP WITH TIME ZONE.
Ich will nun alle Einträge eines Jahres auf ganze Monate herunterbrechen und diese sortieren:
SELECT date_trunc('month', timestampbegin) as ts_month FROM usage_log WHERE timestampbegin >= '2022-01-01 0:00:00.0 +00:00:00'::timestamp AT TIME ZONE 'UTC' AND timestampend <= '2023-01-01 0:00:00.0 +00:00:00' AT TIME ZONE 'UTC' GROUP BY ts_month ORDER BY ts_month;
Laut EXPLAIN ANALYZE scheinen allerdings zuerst alle Einträge sortiert zu werden und erst dann der Filter angewendet, was mutmaßlich zu der sehr langen Abfragezeit führt:
QUERY PLAN
Sort (cost=831703.99…840163.36 rows=3383748 width=8) (actual time=3511.431…3511.432 rows=10 loops=1)
Sort Key: (date_trunc(‘month’::text, timestampbegin))
Sort Method: quicksort Memory: 25kB
→ HashAggregate (cost=422436.45…464733.30 rows=3383748 width=8) (actual time=3502.802…3511.414 rows=10 loops=1)
Group Key: date_trunc(‘month’::text, timestampbegin)
→ Seq Scan on usage_log (cost=0.00…392788.62 rows=11859132 width=8) (actual time=0.017…2532.181 rows=11877088 loops=1)
Filter: ((timestampbegin >= ‘2022-01-01 00:00:00+00’::timestamp with time zone) AND (timestampend <= ‘2023-01-01 00:00:00’::timestamp without time zone))
Rows Removed by Filter: 522831
Planning time: 0.300 ms
Execution time: 3587.046 ms
(10 rows)
Hinzugefügte Indicees auf die timestamp Einträge oder auf den Sort Key (date_trunc('month'::text, timestampbegin)) scheinen nicht aufgegriffen zu werden.
Wie kann ich diese Abfrage optimieren? Ich bin über jeden Input dankbar!
Kleiner Tip wenn Du den Plan entweder mit 4 Leerzeichen einrückst oder ``` in der Zeile davor und danach verwendest, dann bleibt die Struktur (Einrückung) erhalten und ist leichter zu lesen. Alternativ kannst Du ihn auch über https://explain.depesz.com/ verlinken
Deine WHERE Bedingung wird zuerst ausgeführt. Allerdings liefert diese 11877088 von 12399919 Datensätze der Tabelle zurück (also rund 95%). Da hilft kein Index. Der Seq Scan ist die schnellste Methode um diese Anzahl von Datensätzen zu lesen.
du kannst auch mal zum Spaß set enable_seqscan to off; setzen, damit solltest Du einen Indexscan erzwingen. Schau mal, ob der schneller ist - vermutlich nicht.
Danke für den Tip und auch die korrekte Interpretation. Mein Beispiel war scheinbar zu stark vereinfacht, weil ich die falschen Operationen im Blick hatte.
Der eigentliche Anwendungsfall sieht nämlich folgendermaßen aus:
SELECT date_trunc('month', timestampbegin) as ts_month,
COUNT(distinct container) as active_containers
FROM reporting.usage_log
INNER JOIN reporting.containers USING (container)
WHERE code = 123
AND timestampbegin >= '2022-01-01 0:00:00.0 +00:00:00' AT TIME ZONE 'UTC'
AND timestampend <= '2023-01-01 0:00:00.0 +00:00:00' AT TIME ZONE 'UTC'
GROUP BY ts_month
ORDER BY ts_month;
Wenn ich das richtig interpretiere, machen sowohl der Sec Scan wie auch das Sort kaum etwas aus. Die kritischen Operationen sind wohl Gather und GroupAggregate. Ich fürchte hier wird die Anzahl Datensätze einfach zu hoch sein.
Das Gruppieren wird durch Sortieren gelöst - vermutlich weil nicht genug work_mem zur Verfügung steht.
Wenn der Server genug Speicher hat, kannst Du mal versuchen das work_mem für die Session zu erhöhen, z.B.:
set work_mem='250MB';
vielleicht verwendet Postgres dann einen HashAggregate was etwas schneller sein könnte.
Auf was steht denn max_parallel_workers_per_gather? Wenn das nur auf 2 steht und Dein Rechner mehr CPUs hat, kannst Du auch mal versuchen den Wert zu erhöhen:
set max_parallel_workers_per_gather=4;
wenn der Rechner genügend Rechenleistung “übrig” hat, könnte das vielleicht auch helfen.
Den Parameter work_mem hatte ich bereits im Vorfeld deutlich erhöht. Wird er in etwa auf den angezeigten Speicherverbrauch von Memory: 1277569kB gesetzt, geht es deutlich schneller. Mit 2GB konnte ich dann die 5s (statt 8s) erreichen.
Tatsächlich ging es mit set max_parallel_workers_per_gather=4; nochmal eine halbe Sekunde schneller.
Ich denke ich werde neben den Einstellungen auch die Anzahl Datensätze verringern müssen, indem die Intervalle kürzer gewählt werden.