Alternative zu LATERAL und Subquery

[lapadula]

Hallo, ich möchte für jede Person das zuletzt gekaufte Produkt herausfinden.

SELECT
   produkt.*
FROM
   person AS person,
   LATERAL (
   SELECT
      p.id, p.bezeichnung, ...restliche Felder
   FROM
      produkt AS p
   WHERE
      p.person_id = person.id
   ORDER BY
      p.erwerbsdatum DESC LIMIT 1 ) produkt

Subquery:

SELECT
(
   SELECT
      Id
   FROM
      produkt
   WHERE
      produkt.person_id = person.id
   ORDER BY
      erwerbsdatum DESC LIMIT 1 ),
      ...restliche Felder
   FROM
      person;

Die Tabellen dienen nur zur Veranschaulichung und sind frei von der Hand geschrieben und könnne daher Fehler enthalten.

CREATE TABLE person (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

CREATE TABLE produkt (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    person_id BIGINT CONSTRAINT produkt_person_id_fkey REFERENCES person,
    erwerbsdatum: TIMESTAMPTZ,
);

Ich hab ein Problem mit der Performance, wenn ich LATERAL nutze, die Abfrage dauert bei mir fast 1 Minute.
Mit den Subquery funktioniert es unter einer Sek. aber ich brauche alle Felder vom Produkt und die Abfrage wird riesig, da ich in Wirklichkeit viel mehr Felder habe und es evtl. noch mehr werden.

Gibt es da eine Lösung die sowohl schön ist, als auch schnell?

 

[akretschmer]

vergleich mal die Abfragepläne, also mit EXPLAIN (analyse, buffers) ...

 

[castorp]

Ich würde das vermutlich mit distinct on machen:

select per.*, prd.*
from person per
  join (
    select distinct on (person_id) *
    from produkt
    order by person_id, erwerbsdatum desc
  ) prd on prd.person_id = per.id;

 

[lapadula]

Meine Subquery Lösung war falsch, weil ich falsch gejoined habe... die dauert wohl noch länger.

@castorp Deine Lösung funktioniert und ist auch sehr schnell, ich muss es nur noch durchtesten. Danke!

Explain hat folgendes ergeben:

LATERAL:

Nested Loop  (cost=1302.05..73943222.01 rows=56788 width=367)
  ->  Seq Scan on person g  (cost=0.00..1128.88 rows=56788 width=8)
  ->  Limit  (cost=1302.05..1302.05 rows=1 width=367)
        ->  Sort  (cost=1302.05..1302.05 rows=1 width=367)
              Sort Key: b.erwerbsdatum DESC
              ->  Seq Scan on produkt b  (cost=0.00..1302.04 rows=1 width=367)
                    Filter: ((NOT xxx) AND (person_id = g.id))


Distinct:

Merge Join  (cost=10497.04..13621.54 rows=33004 width=484)
  Merge Cond: (person.id = produkt.person_id)
  ->  Index Scan using person_pkey on person  (cost=0.29..2052.03 rows=56788 width=109)
  ->  Unique  (cost=10496.75..10684.95 rows=33004 width=375)
        ->  Sort  (cost=10496.75..10590.85 rows=37641 width=375)
"              Sort Key: produkt.person_id, produkt.erwerbsdatum DESC"
              ->  Seq Scan on produkt  (cost=0.00..1203.03 rows=37641 width=375)
                    Filter: (NOT xxx)

 

[castorp]

Interessanter wäre die Ausgabe von explain (analyze, buffers)

 

[lapadula]

LATERAL:

Nested Loop  (cost=1302.05..73943222.01 rows=56788 width=367) (actual time=8.450..462242.375 rows=33168 loops=1)
  Buffers: shared hit=45828477
  ->  Seq Scan on person g  (cost=0.00..1128.88 rows=56788 width=8) (actual time=0.017..30.935 rows=56788 loops=1)
        Buffers: shared hit=561
  ->  Limit  (cost=1302.05..1302.05 rows=1 width=367) (actual time=8.136..8.136 rows=1 loops=56788)
        Buffers: shared hit=45827916
        ->  Sort  (cost=1302.05..1302.05 rows=1 width=367) (actual time=8.133..8.133 rows=1 loops=56788)
              Sort Key: b.erwerbsdatum DESC
              Sort Method: quicksort  Memory: 25kB
              Buffers: shared hit=45827916
              ->  Seq Scan on produkt b  (cost=0.00..1302.04 rows=1 width=367) (actual time=5.688..8.110 rows=1 loops=56788)
                    Filter: ((NOT xxx) AND (person_id = g.id))
                    Rows Removed by Filter: 39602
                    Buffers: shared hit=45827916
Planning Time: 0.189 ms
Execution Time: 462263.601 ms

DISTINCT

Hash Join  (cost=12335.48..12940.37 rows=33004 width=375) (actual time=53.274..83.323 rows=33168 loops=1)
  Hash Cond: (produkt.person_id = person.id)
"  Buffers: shared hit=1374, temp read=652 written=654"
  ->  Unique  (cost=10496.75..10684.95 rows=33004 width=375) (actual time=37.662..49.722 rows=33169 loops=1)
"        Buffers: shared hit=813, temp read=652 written=654"
        ->  Sort  (cost=10496.75..10590.85 rows=37641 width=375) (actual time=37.657..44.194 rows=37609 loops=1)
"              Sort Key: produkt.person_id, produkt.erwerbsdatum DESC"
              Sort Method: external merge  Disk: 5216kB
"              Buffers: shared hit=813, temp read=652 written=654"
              ->  Seq Scan on produkt  (cost=0.00..1203.03 rows=37641 width=375) (actual time=0.038..8.469 rows=37609 loops=1)
                    Filter: (NOT historisch)
                    Rows Removed by Filter: 1994
                    Buffers: shared hit=807
  ->  Hash  (cost=1128.88..1128.88 rows=56788 width=8) (actual time=15.287..15.287 rows=56788 loops=1)
        Buckets: 65536  Batches: 1  Memory Usage: 2731kB
        Buffers: shared hit=561
        ->  Seq Scan on person  (cost=0.00..1128.88 rows=56788 width=8) (actual time=0.017..5.539 rows=56788 loops=1)
              Buffers: shared hit=561
Planning Time: 1.151 ms
Execution Time: 85.664 ms

Distinct lief sofort durch und Lateral hat über 5 min gebraucht.

 

[castorp]

Ein index auf produkt (person_id) wäre für den Lateral join hilfreich. Aber ich denke auch dann ist der nested loop der für den lateral join verwendet wird immer noch langsamer als die Lösung mit DISTINCT ON ().

Lateral joins helfen typischerweise dann, wenn die äußere Abfrage nur wenige Datensätze liefert, da die "Lateral Abfrage" immer einmal pro äußerem Datensatz ausgeführt werden muss (ähnlich einem co-related Subquery)