Filtern von Timestamps

Hallo!

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;
 GroupAggregate  (cost=425147.55..440761.23 rows=780684 width=16) (actual time=3461.738..5337.649 rows=10 loops=1)
   Group Key: (date_trunc('month'::text, usage_log.timestampbegin))
   ->  Sort  (cost=425147.55..427099.26 rows=780684 width=40) (actual time=3457.584..3741.623 rows=11319706 loops=1)
         Sort Key: (date_trunc('month'::text, usage_log.timestampbegin))
         Sort Method: quicksort  Memory: 1277569kB
         ->  Gather  (cost=1011.49..348740.53 rows=780684 width=40) (actual time=0.218..2312.592 rows=11319706 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Hash Join  (cost=11.49..268533.63 rows=325285 width=40) (actual time=0.106..1526.168 rows=3773235 loops=3)
                     Hash Cond: (usage_log.container = containers.container)
                     ->  Parallel Seq Scan on usage_log  (cost=0.00..254641.49 rows=4940814 width=40) (actual time=0.026..654.563 rows=3959029 loops=3)
                           Filter: ((timestampbegin >= '2022-01-01 00:00:00'::timestamp without time zone) AND (timestampend <= '2023-01-01 00:00:00'::timestamp without time zone))
                           Rows Removed by Filter: 174277
                     ->  Hash  (cost=11.02..11.02 rows=37 width=32) (actual time=0.045..0.046 rows=37 loops=3)
                           Buckets: 1024  Batches: 1  Memory Usage: 11kB
                           ->  Bitmap Heap Scan on containers  (cost=4.56..11.02 rows=37 width=32) (actual time=0.020..0.039 rows=37 loops=3)
                                 Recheck Cond: (code = 123)
                                 Heap Blocks: exact=5
                                 ->  Bitmap Index Scan on code_idx  (cost=0.00..4.55 rows=37 width=0) (actual time=0.012..0.012 rows=37 loops=3)
                                       Index Cond: (code = 123)
 Planning time: 0.202 ms
 Execution time: 5380.966 ms

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.

Ja, war wie vermutet nicht schneller.

1 „Gefällt mir“

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.

Besten Dank für die hilfreichen Tips!

2GB work_mem sollte man aber nur temporär setzen. Auf keinen Fall als dauerhafte Einstellung in postgresql.conf

Gut zu wissen, dann fahr ich vorerst mit den vorgeschlagenen set work_mem='250MB';