Seite 1 von 2 12 LetzteLetzte
Ergebnis 1 bis 10 von 15

Thema: Kann ich diesen Query weiter optimieren, oder wo muss ich meine Hardware aufr�sten

  1. #1
    josch ist offline Neuer Benutzer
    Registriert seit
    10.10.2011
    Beiträge
    7

    Standard Kann ich diesen Query weiter optimieren, oder wo muss ich meine Hardware aufr�sten

    Hallo,

    ich schicke einen Query auf eine ganze Menge Datensätze. Und da ich das ganze auch noch nach einem aggregierten Wert sortiere, wird mir das leider zu langsam. Ich suche nun nach einer Möglichkeit, meine Abfrage weiter zu optimieren, oder eine Beratung, welchen Serverteil ich aufrüsten muss, damit das Teil flutscht.

    Ich beschreib euch das ganze mal:

    Ich habe 3 Tabellen:

    bauteile (ca. 5.000 Datensätze)
    id name
    1 Transistor
    2 Klebeband
    3 Handtuch
    4 Schrauben

    produkte (ca. 500.000 Datensätze)
    id name
    1 Radio
    2 Fernseher
    3 Hubschrauber
    4 Dampfmaschine

    zuordnung (ca. 5.000.000 Datensätze)
    id produkt_id bauteil_id
    1 2 4
    2 2 1
    3 3 2
    4 4 3


    Soweit so simpel.


    Ich möchte nun verschiedene Bauteile angeben können, und dann alle Produkte zurückbekommen, für die mindestens eines der Bauteile benötigt wird. Das ist auch ansich kein Problem, mit einem Limit (z.B. 1000) im Query braucht das wenige Millisekunden.

    Aber wie man sich vorstellen kann, möchte ich nicht irgendwelche Datensätze, sondern ich möchte die Datensätze sortiert haben. Und zwar nach der Anzahl der für das jeweilige Produkt fehlenden Bauteile.

    Tja, und weil dann für alle Treffer noch die Anzahl der fehlenden Items ermittelt werden muss, wird das ganze laaaaangsam. Wenn ich 100 Bauteile angebe, dauert es auf meinem Server (AMD Athlon II X4 Quad-Core 4x 2,3 GHz, 16 GB DDR3-RAM) ca. 3-5 Sekunden.

    Mein Query:
    Code:
    WITH bauteil_ids AS (
        SELECT id 
        FROM bauteile
        WHERE id IN ((3145),(4466),(4323),(4452),(3095),(3195),(4131),(3140),(3189),(4512))
    )
    SELECT 
        produkt_id,
        count(*) as gesamtbauteilcount,
        SUM(CASE WHEN not EXISTS (SELECT id FROM ingredients WHERE id = ingredient_id) 
            THEN 1 ELSE 0 END) AS missingbauteilcount
    FROM zuordnung
    GROUP BY produkt_id
    ORDER BY missingbauteilcount ASC, gesamtbauteilcount DESC
    LIMIT 20

    Meine Frage ist also:

    1. Kann ich am Query noch irgendwas wesentlich besser machen?
    2. Wo muss ich meinen Server aufrüsten, damit das ganze schneller wird? CPU, Ram? Ganz was anderes?

    Mein Ziel ist, die ganze Abfrage auf <1 Sekunde zu bekommen, bei der oben angegebenen Anzahl an Datensätzen.

    Vielen Dank für eure Hilfe!
    Geändert von josch (10.10.2011 um 23:43 Uhr)

  2. #2
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    Was ist die Datenbankkonfiguration (relevante Parameter)?
    Was ist die Ausgabe des Query Plans?
    Wie sieht die Tabellendefinition aus?

    Oh, und wenn du dein Posting von irgendwo anders mit Copy&Paste einfügst, dann schau doch mal vor dem Posten nach, ob die Umlaute passen.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  3. #3
    josch ist offline Neuer Benutzer
    Registriert seit
    10.10.2011
    Beiträge
    7

    Standard

    Datenbankkonfiguration:
    Code:
    shared_buffers = 4GB
    work_mem = 64MB
    effective_cache_size = 12GB
    random_page_cost = 1.0



    Query Plan:
    Code:
    Limit  (cost=216637.55..216637.60 rows=20 width=8) (actual time=4287.988..4287.994 rows=20 loops=1)
      CTE bauteil_ids
        ->  Bitmap Heap Scan on bauteile  (cost=9.59..17.81 rows=10 width=4) (actual time=0.049..0.060 rows=10 loops=1)
              Recheck Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
              ->  Bitmap Index Scan on bauteile_id  (cost=0.00..9.59 rows=10 width=0) (actual time=0.039..0.039 rows=10 loops=1)
                    Index Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
      ->  Sort  (cost=216619.74..217335.28 rows=286215 width=8) (actual time=4287.987..4287.989 rows=20 loops=1)
            Sort Key: (sum(CASE WHEN (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) THEN 1 ELSE 0 END)), (count(*))
            Sort Method:  top-N heapsort  Memory: 26kB
            ->  HashAggregate  (cost=140312.06..209003.66 rows=286215 width=8) (actual time=3918.583..4157.986 rows=501302 loops=1)
                  ->  Seq Scan on zuordnung  (cost=0.00..103155.32 rows=4954232 width=8) (actual time=0.012..807.174 rows=4954232 loops=1)
                  SubPlan 2
                    ->  CTE Scan on bauteil_ids  (cost=0.00..0.22 rows=1 width=0) (never executed)
                          Filter: (id = $1)
                  SubPlan 3
                    ->  CTE Scan on bauteil_ids  (cost=0.00..0.20 rows=10 width=4) (actual time=0.052..0.068 rows=10 loops=1)
    Total runtime: 4289.296 ms

    Tabellendefinitionen:

    Bauteile:
    Code:
    CREATE TABLE bauteile
    (
      id serial NOT NULL,
      "name" character varying(255) NOT NULL,
      CONSTRAINT bauteile_pkey PRIMARY KEY (id),
      CONSTRAINT bauteile_name_uniq UNIQUE (name)
    )
    WITH (
      OIDS=FALSE
    );
    
    CREATE UNIQUE INDEX bauteile_id
      ON bauteile
      USING btree
      (id);
    

    Produkte:
    Code:
    CREATE TABLE produkte
    (
      id serial NOT NULL,
      "name" character varying(255),
      CONSTRAINT produkte_pkey PRIMARY KEY (id),
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE produkte OWNER TO defaultuser;
    Zuordnung:
    Code:
    CREATE TABLE zuordnung
    (
      id serial NOT NULL,
      produkt_id integer NOT NULL,
      bauteil_id integer,
      CONSTRAINT zuordnung_pkey PRIMARY KEY (id),
      CONSTRAINT bauteil_id_refs_id_1ada94fb3ba2efc3 FOREIGN KEY (bauteil_id)
          REFERENCES bauteile (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
      CONSTRAINT produkt_id_refs_id_45191a629e4145fe FOREIGN KEY (produkt_id)
          REFERENCES produkte (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    )
    WITH (
      OIDS=FALSE
    );
    
    CREATE UNIQUE INDEX zuordnung_id_idx
      ON zuordnung
      USING btree
      (id)
      WITH (FILLFACTOR=10);
    
    CREATE INDEX zuordnung_bauteil_id
      ON zuordnung
      USING btree
      (bauteil_id)
      WITH (FILLFACTOR=10);
    
    CREATE INDEX zuordnung_produkt_id
      ON zuordnung
      USING btree
      (produkt_id)
      WITH (FILLFACTOR=10);

    So, und nun noch zu der Sache mit den Umlauten:

    Ich habe den Text schön im Browser (Chrome) geschrieben und nicht rumkopiert. Als ich den Beitrag abgeschickt habe, war ich bereits ausgeloggt, musste mich also neu einloggen. Danach wurde mein Beitrag automatisch gepostet (so wie es sein soll), aber die Umlaute waren zerhackt.
    Und ja, ich würde das jemand der es mir in meinem Forum erzählt auch nicht glauben und mir denken, "der hat bestimmt irgendwas falsch gemacht", aber es war genau so
    Geändert von josch (11.10.2011 um 00:27 Uhr)

  4. #4
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    524

    Standard

    Vielleicht is es ja doch ein Copy & Paste fehler, aber in Deinem Statement verwendest Du das Ergebnis der CTE (with) nicht. bauteil_id wird nicht referenziert.

    Und mir ist auch nicht klar wozu Du das WITH überhaupt brauchst. Die Liste der IDs (Du musst übrigens nicht jede ID In Klammern setzen) könnte man doch auch direkt in einem IN beim "normalen" SELECT verwenden.
    A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

  5. #5
    josch ist offline Neuer Benutzer
    Registriert seit
    10.10.2011
    Beiträge
    7

    Standard

    Zitat Zitat von castorp Beitrag anzeigen
    Vielleicht is es ja doch ein Copy & Paste fehler, aber in Deinem Statement verwendest Du das Ergebnis der CTE (with) nicht. bauteil_id wird nicht referenziert.

    Und mir ist auch nicht klar wozu Du das WITH überhaupt brauchst. Die Liste der IDs (Du musst übrigens nicht jede ID In Klammern setzen) könnte man doch auch direkt in einem IN beim "normalen" SELECT verwenden.
    Oh, tut mir leid, das ist in der Tat ein copy & paste fehler, so ist es richtig:

    Code:
    WITH bauteil_ids AS (
        SELECT id 
        FROM bauteile
        WHERE id IN ((3145),(4466),(4323),(4452),(3095),(3195),(4131),(3140),(3189),(4512))
    )
    SELECT 
        produkt_id,
        count(*) as gesamtbauteilcount,
        SUM(CASE WHEN not EXISTS (SELECT id FROM bauteil_ids WHERE id = bauteil_id) 
            THEN 1 ELSE 0 END) AS missingbauteilcount
    FROM zuordnung
    GROUP BY produkt_id
    ORDER BY missingbauteilcount ASC, gesamtbauteilcount DESC
    LIMIT 20
    Die Sache mit dem with ergibt sich aus den Zusammenhang in dem ich das verwende, ich habe das der einfachheit halber auf das wesentliche Problem runtergebrochen. Da ist normalerweise noch ein Query drin, was sich bestimmte IDs holt.
    Wenn ich die IDs einfach als IN statement in mein Query hole ändert sich an der Geschwindigkeit aber nichts.
    Geändert von josch (11.10.2011 um 10:24 Uhr)

  6. #6
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    524

    Standard

    Du könntest mal versuchen das co-related subselect durch einen left join zu ersetzen. Sowas in der Art:
    Code:
    WITH bauteil_ids AS (
        SELECT id 
        FROM bauteile
        WHERE id IN ((3145),(4466),(4323),(4452),(3095),(3195),(4131),(3140),(3189),(4512))
    )
    SELECT 
        z.produkt_id,
        count(z.produkt_id) as gesamtbauteilcount,
        SUM(CASE WHEN bi.id IS NULL THEN 1 ELSE 0 END) AS missingbauteilcount
    FROM zuordnung z
      LEFT JOIN bauteil_ids bi ON bi.id = z.bauteil_id
    GROUP BY z.produkt_id
    ORDER BY missingbauteilcount ASC, gesamtbauteilcount DESC
    LIMIT 20
    Ich glaube zwar nicht, dass das wesentlich am Execution plan ändert, aber einen Versuch ist es wert.
    Geändert von castorp (11.10.2011 um 11:54 Uhr)
    A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

  7. #7
    josch ist offline Neuer Benutzer
    Registriert seit
    10.10.2011
    Beiträge
    7

    Standard

    Zitat Zitat von castorp Beitrag anzeigen
    Du könntest mal versuchen das co-related subselect durch einen left join zu ersetzen. Sowas in der Art:
    Code:
    WITH bauteil_ids AS (
        SELECT id 
        FROM bauteile
        WHERE id IN ((3145),(4466),(4323),(4452),(3095),(3195),(4131),(3140),(3189),(4512))
    )
    SELECT 
        z.produkt_id,
        count(z.produkt_id) as gesamtbauteilcount,
        SUM(CASE WHEN bi.id IS NULL THEN 1 ELSE 0 END) AS missingbauteilcount
    FROM zuordnung z
      LEFT JOIN bauteil_ids bi ON bi.id = z.bauteil_id
    GROUP BY z.produkt_id
    ORDER BY z.missingbauteilcount ASC, z.gesamtbauteilcount DESC
    LIMIT 20
    Ich glaube zwar nicht, dass das wesentlich am Execution plan ändert, aber einen Versuch ist es wert.
    Danke, 200 ms schneller. 3,45s braucht der alte, 3,25 mit deiner Abwandlung:

    Code:
    Limit  (cost=176508.36..176508.41 rows=20 width=8) (actual time=5070.237..5070.243 rows=20 loops=1)  CTE bauteil_ids
        ->  Bitmap Heap Scan on bauteile  (cost=9.59..17.81 rows=10 width=4) (actual time=0.046..0.056 rows=10 loops=1)
              Recheck Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
              ->  Bitmap Index Scan on bauteile_id  (cost=0.00..9.59 rows=10 width=0) (actual time=0.038..0.038 rows=10 loops=1)
                    Index Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
      ->  Sort  (cost=176490.55..177206.09 rows=286215 width=8) (actual time=5070.236..5070.238 rows=20 loops=1)
            Sort Key: (sum(CASE WHEN (bi.id IS NULL) THEN 1 ELSE 0 END)), (count(*))
            Sort Method:  top-N heapsort  Memory: 26kB
            ->  HashAggregate  (cost=164581.25..168874.47 rows=286215 width=8) (actual time=4699.328..4940.101 rows=508899 loops=1)
                  ->  Hash Left Join  (cost=0.33..126181.17 rows=5120011 width=8) (actual time=0.095..2546.413 rows=5047722 loops=1)
                        Hash Cond: (zuordnung.bauteil_id = bi.id)
                        ->  Seq Scan on zuordnung  (cost=0.00..106607.11 rows=5120011 width=8) (actual time=0.012..836.644 rows=5047722 loops=1)
                        ->  Hash  (cost=0.20..0.20 rows=10 width=4) (actual time=0.070..0.070 rows=10 loops=1)
                              Buckets: 1024  Batches: 1  Memory Usage: 1kB
                              ->  CTE Scan on bauteil_ids i  (cost=0.00..0.20 rows=10 width=4) (actual time=0.050..0.062 rows=10 loops=1)
    Total runtime: 5071.608 ms"
    Habt ihr noch mehr Ideen?

  8. #8
    josch ist offline Neuer Benutzer
    Registriert seit
    10.10.2011
    Beiträge
    7

    Standard

    Hmm, ok, anscheinend ist der Query nicht weiter zu optimieren.

    Was brauche ich für Hardware, um diesen Query schneller zu bekommmen? Brauche ich noch mehr RAM? Oder doch mehr Cores? Oder bringen mehr Cores nur etwas für viele Anfragen gleichzeitig, und ich brauche schnellere Cores?

  9. #9
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von josch Beitrag anzeigen
    Hmm, ok, anscheinend ist der Query nicht weiter zu optimieren.

    Was brauche ich für Hardware, um diesen Query schneller zu bekommmen? Brauche ich noch mehr RAM? Oder doch mehr Cores? Oder bringen mehr Cores nur etwas für viele Anfragen gleichzeitig, und ich brauche schnellere Cores?
    Vermutlich mehr RAM und schnellere Platten. Du könntest das EXPLAIN mit BUFERS ON fahren, da sieht man noch besser, wie die Hitrate Shared Buffers ist. Cores nützen bei einer Anfrage nicht, nur wenn davon viele gleichzeitig laufen.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  10. #10
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Cores nützen bei einer Anfrage nicht, nur wenn davon viele gleichzeitig laufen.
    Ich hab da was richtig performantes im Angebot.
    Leider hostet das deine Firma nicht ;-)
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

Seite 1 von 2 12 LetzteLetzte

Ähnliche Themen

  1. Antworten: 17
    Letzter Beitrag: 22.12.2011, 12:36
  2. Frage Wie kann ein Array mittels Domain oder Funktion validiert werden?
    Von jazzhunter im Forum Trigger und Funktionen
    Antworten: 5
    Letzter Beitrag: 26.07.2011, 17:48
  3. Antworten: 0
    Letzter Beitrag: 10.01.2011, 10:26
  4. Antworten: 4
    Letzter Beitrag: 07.11.2007, 11:42
  5. Beste Hardware Ausstattung???
    Von werner im Forum Installation von PostgreSQL
    Antworten: 3
    Letzter Beitrag: 29.08.2007, 11:36

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum