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)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Transistor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Klebeband[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Handtuch[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Schrauben[/TD]
[/TR]
[/TABLE]

produkte (ca. 500.000 Datensätze)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Fernseher[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hubschrauber[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dampfmaschine[/TD]
[/TR]
[/TABLE]

zuordnung (ca. 5.000.000 Datensätze)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]produkt_id[/TD]
[TD]bauteil_id[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[/TABLE]


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:

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!

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.

Datenbankkonfiguration:

shared_buffers = 4GB
work_mem = 64MB
effective_cache_size = 12GB
random_page_cost = 1.0

Query Plan:

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:

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);[B]

Produkte:
[/B]

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:

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

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:

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.

Du könntest mal versuchen das co-related subselect durch einen left join zu ersetzen. Sowas in der Art:

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.

Danke, 200 ms schneller. 3,45s braucht der alte, 3,25 mit deiner Abwandlung:

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?

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

Ich hab da was richtig performantes im Angebot.
Leider hostet das deine Firma nicht :wink:

Hier liegt bis jetzt noch keine Anfrage Deiner Firma nach Hosting vor …

So what?

Andreas

Hab den Query mal mit “Buffers On” Explain laufen lassen:

Limit  (cost=236550.97..236551.02 rows=20 width=8) (actual time=4960.441..4960.449 rows=20 loops=1)
  Buffers: shared hit=66210
  CTE bauteil_ids
    ->  Bitmap Heap Scan on bauteile  (cost=9.59..17.81 rows=10 width=4) (actual time=0.109..0.134 rows=10 loops=1)
          Recheck Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
          Buffers: shared hit=27
          ->  Bitmap Index Scan on bauteile_id  (cost=0.00..9.59 rows=10 width=0) (actual time=0.087..0.087 rows=10 loops=1)
                Index Cond: (id = ANY ('{3145,4466,4323,4452,3095,3195,4131,3140,3189,4512}'::integer[]))
                Buffers: shared hit=21
  ->  Sort  (cost=236533.15..237217.85 rows=273879 width=8) (actual time=4960.439..4960.442 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
        Buffers: shared hit=66210
        ->  HashAggregate  (cost=163514.37..229245.33 rows=273879 width=8) (actual time=4546.799..4814.703 rows=549890 loops=1)
              Buffers: shared hit=66208
              ->  Seq Scan on zuordnung  (cost=0.00..121800.07 rows=5561907 width=8) (actual time=0.033..1145.416 rows=5442128 loops=1)
                    Buffers: shared hit=66181
              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.111..0.138 rows=10 loops=1)
                      Buffers: shared hit=27
Total runtime: 4967.197 ms

Soweit ich den “Buffers On” Explain verstanden habe, liest er da nichts mehr von der Platte, oder sehe ich das falsch? Die Zahl ist immer mit “shared hit=XXX” angegeben.

Wir haben 4 GB für die Shared Buffers reserviert, die Datenbank ist gedumpt ungefähr 400 MB groß.

Wir hatten am Anfang 4 GB Ram. Beim Sprung von 4 GB auf 8 GB ist der Query viel schneller geworden, dann haben wir von 8 GB auf 16 GB hochgerüstet, und da war es dann nur noch ca. 1 Sekunde Unterschied.

Noch mehr Speicher und eine schnellere Platte (SSD) z.B. würden da doch auch nicht mehr weiterhelfen, wenn die komplette Datenbank schon im RAM ist, oder sehe ich das falsch?

Exakt.

Andreas

Ja, aber was kann ich denn da machen? Heißt das, dieser Query kann niemals unter den erreichten 3-4 Sekunden ausgeführt werden? Es gibt doch weit größere Datenbanken als meine, das muss doch irgendwie gehen…

Na, ja, ich würde mal schauen, wie du diese Zeiten heruntergebrochen bekommst.
Da braucht die reine I/O-Leistung deines Systems schon über eine Sekunde, um die Tabelle sequentiell zu durchlaufen.

Die Aggregierung will dann eine halbe Million Zeilen bearbeiten, das kostet einerseits Zeit, andererseits Speicher.
Siehst ja, wie schnell du da auf 5 Sekunden kommst.