Join mit Index und Group By

Hallo,

ich versuche gerade ein minimal Beispiel zu bauen, es sieht aber nicht genau so aus wie bei meinem Problem. Zu dem Problem:
Ich möchte eine Tabelle mit Daten joinen mit einer anderen Tabelle, die das Grouping vorgibt.
Dann sollen die Daten aggregiert werden (bisher über Group By).

Das minimal-Beispiel ist auf db-fiddle: Postgres 14 | db<>fiddle zu finden.

Tabelle groupst mit grid und id2 bestimmt das Grouping (Primary Key/index: (id2, grid)).
Tabelle ids enthält die Daten (id2) zu speziellen id1. Auch hier primary key: (id2, id1).

Nun werden diese Daten über einen Nested Loop gejoint (bei mir nutzt er für beide Tabellen einen Index Scan).
Das Group By geht über id1 und grid, also über beide Tabellen hinweg.
Um das gescheit gruppieren zu können, müssen die Daten dementsprechend sortiert sein.

Das ist die der Teil der Ausgabe (Analyse), der sich um das sortieren kümmert:
Sort Key: ids.id1, groupst.grid
Presorted Key: ids.id1

  1. Frage:
    Warum erkennt das System nicht, dass der Zugriff über den Index der Tabelle (groupst mit index groupst_pkey), schon nach grid sortiert ist, also auch nach groupst.grid?

2.Frage:
Habt ihr einen besseren Ansatz für solche Anforderungen?

Vielen Dank für eure Hilfe!
Beste Grüße, Ludwig Rahlff

Hmm, bei mir lokal mit Postgres 16 kommt folgender Plan (auf dbfiddle funktioniert es mit Postgres 16 leider nicht):

HashAggregate  (cost=105917.14..160505.98 rows=1499460 width=53) (actual time=1541.196..2979.206 rows=1500000 loops=1)
  Group Key: ids.id1, groupst.grid
  Planned Partitions: 1024  Batches: 1025  Memory Usage: 9329kB  Disk Usage: 196568kB
  Buffers: shared hit=3181, temp read=20750 written=45245
  I/O Timings: temp read=217.918 write=490.079
  ->  Hash Join  (cost=6430.00..17834.53 rows=3499247 width=26) (actual time=49.364..391.887 rows=3500000 loops=1)
        Hash Cond: ((groupst.id2)::text = (ids.id2)::text)
        Buffers: shared hit=3181
        ->  Seq Scan on groupst  (cost=0.00..1.07 rows=35 width=14) (actual time=0.014..0.069 rows=35 loops=1)
              Buffers: shared hit=1
        ->  Hash  (cost=4180.00..4180.00 rows=500000 width=17) (actual time=48.386..48.387 rows=500000 loops=1)
              Buckets: 524288  Batches: 1  Memory Usage: 28456kB
              Buffers: shared hit=3180
              ->  Seq Scan on ids  (cost=0.00..4180.00 rows=500000 width=17) (actual time=0.011..14.695 rows=500000 loops=1)
                    Buffers: shared hit=3180
Planning Time: 0.192 ms
Execution Time: 3145.986 ms

Ok, besten Dank, dann werde ich mal schauen, dass ich mir auch einen PostgreSQL16 Dienst aufsetze.

– Edit
Ja, mit Postgresql 16 scheint er auch bei mir ein HashAggregate zu nutzen.

Hintergrund:
Ich möchte Daten aggregieren, um weitere Werte zu berechnen. Die Daten liegen in einer Hierarchischen Struktur vor (in der folgenden Anfrage vernachlässigt). Dabei soll es Beispielsweise um Messpunkte (bspw. Spannung, Streckung) gehen, die jeweils zu einer Messreihe gehören. Nun möchte ich auf Messreihen-Ebene mit diesen Daten rechnen. Damit die Zuordnung der jeweiligen Parameter zueinander nicht verloren geht, habe ich diese in einem json aggregiert (1. Teil im 3. Anfrageblock).
Die Messreihen gehören zu einer Probe, an der mehrere Messungen durchgeführt wurden.
Nun möchte ich etwas berechnen aus den verschiedenen Messreihen, aber immer mit der gleichen Dehnung. Dafür gibt es die Tabelle complex_groupcond, in der beschrieben wird nach welchen Parametern gruppiert werden soll (dies können auch mehrere sein, was zu dem folgenden Problem führt).

Meine Anfrage ist doch etwas komplexer:

Auch hier scheint er das (äußere) Group by mit einem Hash-Algorithmus zu lösen.
Ich möchte alle Werte aus values in der inneren Anfrage nur einmal haben (daher der distinct), aber aus dem value (json) Werte für das Group by in der äußeren Anfrage konkateniert bereitstellen. Da er den Kram joint (mit keinem Unique key), können die Werte aus values “verdoppelt” werden. Daher brauch in den Distinct, für den quasi auch (wie für die window function) sortiert werden muss.

  1. Frage: Warum muss er überhaupt sortieren? Da er den Index complex_in_pkey nutzt weiß kommt die gejointe Tabelle ja schon sortiert dazu?

  2. Frage: Ist mein Ansatz der Falsche zur Lösung des Problems? Wäre es besser mit einem unique constraint zu joinen, sodass die Zeilen nicht verdoppelt werden?
    Dann bräuchte ich eine Funktion die den json nimmt und ein aggregat aus complex_groupcond, und damit die group condition bildet. Meinen Sie/Meint ihr das wäre ein besserer Ansatz?

  3. Frage: Oder wäre es sinnvoller alle Parameter blank weiterzugeben, und die hierarchie zu tracken und anhand dieser für die Berechnung zu sortieren?

Vielen Dank,
Ludwig Rahlff