Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Alternative zu LATERAL und Subquery

Dieses Thema im Forum "PostgreSQL" wurde erstellt von lapadula, 10 November 2020.

  1. lapadula

    lapadula Aktiver Benutzer

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

    Code:
    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:

    Code:
    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.

    Code:
    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?
     
  2. akretschmer

    akretschmer Datenbank-Guru

    vergleich mal die Abfragepläne, also mit EXPLAIN (analyse, buffers) ...
     
    lapadula gefällt das.
  3. castorp

    castorp Datenbank-Guru

    Ich würde das vermutlich mit distinct on machen:

    Code:
    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 und dabadepdu gefällt das.
  4. lapadula

    lapadula Aktiver Benutzer

    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:

    Code:
    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)
     
  5. castorp

    castorp Datenbank-Guru

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

    lapadula Aktiver Benutzer

    Code:
    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.
     
  7. castorp

    castorp Datenbank-Guru

    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)
     
    lapadula und akretschmer gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden