Postgresql: Query Planner bei Subselect auf die Sprünge helfen?

[Forumsgast]

Hallo zusammen,

bin gerade verwirrt durch folgendes Problem.

Ich habe diese Abfrage:

SELECT some_field from rows where job_id = 111 ORDER BY row_id ASC LIMIT 20;

Dauert ca. 1s, passt also.

Nun möchte ich aber statt konstanter ID 111 die job_id in einer Subquery auslesen:

SELECT some_field from rows where job_id = (SELECT 111 LIMIT 1) ORDER BY row_id ASC LIMIT 20;

Und die Dauer springt plötzlich total in die Höhe auf ein paar Minuten. (Die echte Subquery ist natürlich deutlich komplexer.) Was ist da das Problem, dass das ganze so lange dauert?

Query Plan sieht so aus:

Limit  (cost=0.45..5228.88 rows=20 width=20)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.01 rows=1 width=4)
          ->  Result  (cost=0.00..0.01 rows=1 width=4)
  ->  Index Scan using rows_pkey on rows  (cost=0.44..6637232.95 rows=25389 width=20)
        Filter: (job_id = $0)

Kann mich jemand bitte von der Leitung runterholen und mir sagen woran's hapert?

[Forumsgast]

Kann verschiedene Ursachen haben. Im ersten Fall sieht der Planner eine Konstante für job_id, im zweiten Fall nicht. Was hier nicht hilft ist, daß Du nur etwas zeigst, was letztendlich nicht die reale Tabelle ist. Helfen würde die Definition der realen Tabelle und ein EXPLAIN (ANALYSE, BUFFERS) beider Abfragen, sowie auch die verwedete PG-Version.

test=*# create table foo(id serial primary key, val numeric);
CREATE TABLE
test=*# insert into foo (val) select random() from generate_series(1,1000000) s;
INSERT 0 1000000
test=*# explain analyse select * from foo where id = 4711;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pkey1 on foo  (cost=0.42..8.44 rows=1 width=36) (actual time=0.038..0.039 rows=1 loops=1)
   Index Cond: (id = 4711)
 Planning time: 2.446 ms
 Execution time: 0.069 ms
(4 Zeilen)

test=*# explain analyse select * from foo where id = (select 4711 limit 1);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pkey1 on foo  (cost=0.43..8.45 rows=1 width=36) (actual time=0.026..0.028 rows=1 loops=1)
   Index Cond: (id = $0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning time: 0.130 ms
 Execution time: 0.063 ms
(7 Zeilen)

test=*#

[Forumsgast]

Ich habe diese Abfrage:

SELECT some_field from rows where job_id = 111 ORDER BY row_id ASC LIMIT 20;

passt

statt konstanter ID 111 die job_id in einer Subquery

SELECT some_field from rows where job_id = (SELECT 111 LIMIT 1) ORDER BY row_id ASC LIMIT 20;

Dauer ein paar Minuten. (Die echte Subquery ist natürlich deutlich komplexer.)

Ich würde akretschmer da Recht geben. Der entscheidende Hintergrund zur Beantwortung der Frage fehlt: Die Subquery selbst. Auch wenn Du die vielleicht nicht angeben möchtest, es fehlt auch die Angabe zur Ausführungsdauer der Subquery allein.

Unabhängig von der Komplexität der Subquery würde ich aber auch eine Query/ Subquery so nicht formulieren. Die Angabe der Konstante in Variante 1 ist natürlich okay.
Die Variante 2 mit "where <feld> = (Select .." find ich recht ungewöhnlich. Mag sein, dass es formal korrekt/erlaubt ist, aber bei mir sträubt es sich da. Ich kann dazu auch keine konkreten Angaben machen, warum.

Eine Problematik wäre jedenfalls:
Geläufiger ist die Abfrage Variante 2 mit dem Konstrukt "where <feld> in (select <feld>", also "in" statt "=" Operator. Das deutet syntaktisch zumindest an, dass das Subselect verschiedene Werte liefern kann/könnte. (Genau wie die unbekannte Subquery bei Dir) Während hier mit dem = Operator die "Erwartungshaltung" eher bei einem(1) Wert liegt.
So jetzt die Kurve zum Optimizer: Ich habe mir noch nie die Mühe gemacht, Optimizer Logik zu hinterfragen. Noch reden wir jedenfalls von menschengemachten Algorithmen-keine KI-, die mit endlicher Mühe, idR. nach (dringendstem) Bedarf entwickelt werden, auf konkrete Fälle hin.
Ein Optimizer wird also gut mit einem klassischen Join umgehen können, das ist "seine Standardaufgabe".
Weniger gut mit einem "where .. in .." was m.E. historisch für eine Werteauflistung steht, also z.B. "where <feld> in (1,2,3)"
Noch weniger gut, wird er evtl. die oben verwendete Syntax optimieren, weil sie ungewöhnlich ist.

Noch was:
Subqueries "riechen" immer sehr nach Whileschleife und wenig Optimierung. Die Umsetzung erfolgt algorithmisch oft so, dass (ganz stumpf) für jeden Satz aus der Menge A, die Operation aus der Menge B wiederholt wird.
Wie gesagt, ich kenne keine Optimizer Implementierung, aber kann sowas gelegentlich am Laufzeitverhalten erkennen.

Kann sein, dass meine Erfahrungswerte hier nicht zutreffen, aber ich würde aus der Abfrage oben einen stinknormalen Join machen. (Und natürlich die "komplexe Subquery" selbst auch entsprechend umstellen, wenn dort solche Konstrukte beinhaltet sind).
Und wenn möglich, auch das Limit 1 in der Subquery vermeiden. Entweder mit Max.. Group by oder mit einer Window Function. Das ist aber ohne Kenntnis der Query absolut vage.

[Forumsgast]

Ich kann natürlich auch die komplexere Subquery zeigen, allerdings tritt das Problem bei mir schon in oben genanntem Beispiel auf. Abgesehen davon, dass ich andere Werte selektiere, sieht meine Abfrage genau so aus.

Perry Staltic

Die "komplexe Subquery" ist in Wahrheit auch nichts großartiges, das sind lediglich zwei Joins - da geht's um ein Mapping von einem Namen zur ID. Ich habe das ausgelassen, weil es für die Reproduzierbarkeit bei mir keinen Unterschied gemacht hat und den Query Plan nur mühsamer zu lesen gemacht hätte. Die Subquery selbst braucht ca. 50ms. Die Subquery steckt nur da drinnen, weil ich sonst zwei Queries bräuchte und die Abfrage auf einen Remote DB-Server gehen und ich daher möglichst wenig Queries/Netzwerkverkehr möchte. Der Query-Planer sollte die Abfrage idealerweise als Konstante sehen, es wird drinnen auf keine Werte der äußeren Abfrage zugegriffen.

akretschmer
Ich verwende PG 9.6. Die Explains schauen so aus:

EXPLAIN (BUFFERS,ANALYZE)  SELECT * from rows WHERE job_id = (SELECT 6965 as f LIMIT 1)
                        ORDER BY row_id ASC LIMIT 20;
---------------
Limit  (cost=0.45..5228.88 rows=20 width=20) (actual time=383069.066..383069.570 rows=20 loops=1)
  Buffers: shared hit=10178155 read=3369863
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.01 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
  ->  Index Scan using rows_pkey on rows  (cost=0.44..6637232.95 rows=25389 width=20) (actual time=383069.061..383069.535 rows=20 loops=1)
        Filter: (job_id = $0)
        Rows Removed by Filter: 24821280
        Buffers: shared hit=10178155 read=3369863
Planning time: 0.133 ms
Execution time: 383069.614 ms



EXPLAIN (BUFFERS, ANALYSE) SELECT * from rows where job_id = 6965
                         LIMIT 20;
---------------
Limit  (cost=0.44..5.64 rows=20 width=12) (actual time=34.217..34.274 rows=20 loops=1)
  Buffers: shared hit=13 read=3
  ->  Index Scan using job_id_idx on rows  (cost=0.44..2562.24 rows=9848 width=12) (actual time=34.214..34.247 rows=20 loops=1)
        Index Cond: (job_id = 6965)
        Buffers: shared hit=13 read=3
Planning time: 0.113 ms
Execution time: 34.315 ms

Tabellenstruktur von rows sieht so aus:

 
 create table rows (  row_id bigserial not null   constraint rows_pkey    primary key,  job_id integer not null   constraint fk_rows    references jobs,  data jsonb,  created_at timestamp default now() not null, ) ;    
 create index job_id_idx  on rows (job_id) ;

Offenbar nimmt er unterschiedliche Indizes, einmal richtigerweise den der auf job_id liegt und einmal einen kompletten Index Scan über den Primärschlüssel, wo er dann 24 Millionen rows wegfiltern muss...

[Forumsgast]

da, wo der Plan schlecht ist, wählt er einen konservativeren Plan, da er zur Plaungszeit das Resultat von "SELECT 6965 as f LIMIT 1" nicht kennt. Dieses Select liefert $0, was dann wieder als Filterkriterium im anderen Subplan verwendet. Dort erwartet er 25389 Rows, die offenbar (JSONB) auch recht groß sind (im Schnitt). Daher erscheint die Verwendung des PK-Indexes sinnvoll, da dieser auch für die sortierung dann dienen kann. Leider irrt er bei der erwarteten Ergebnissmenge, statt 25389 Rows kommen da nur 20.

Vermutlich gibt es auch job_id's, die deutlich häufiger vorkommen, oder?

Auch beim 'schnellen' explain irrt er sich, aber nicht ganz so schlimm. Es könnte helfen, die Statistiken für die job_id* Spalte zu erhöhen. Autoanalyse läuft regelmäßig, oder?

Es könnte auch sinnvoll sein, erst einmal nur die row_id's zu ermitteln, die man haben will, und erst dann alle anderen Spalten zu holen. Könnte man als CTE machen, also in etwa: (untested)

with tmp_row_ids as (select row_id from rows where job_id = (select 6595 as f) order by row_id limit 20) select * from rows where row_id in (select * from tmp_row_ids) order by ...

[Forumsgast]

Vermutlich gibt es auch job_id's, die deutlich häufiger vorkommen, oder?

Ja, danke, das ist ein guter Punkt. Hab mal eine deutlich häufigere job_id (500.000 Vorkommen) genommen und es dauert gleich viel länger, auch mit konstantem Wert als Bedingung im WHERE. In Zukunft werden die Jobs aber wohl häufiger und kleiner, d.h. die meisten Jobs werden eher 20 als 1000+ zugehörige Einträge haben.

Das mit dem Selektieren ist ein guter Tipp, werde ich mal probieren.
Besonders werde ich mir auch die Statistiken und Auto Analyse anschauen. Ich nehme an du sprichst von https://www.postgresql.org/docs/9.6/...OR-STATISTICS? Habe bis jetzt weniger mit der DB-Administration zu tun gehabt, werde mal schauen was da so getan wird.

Danke für die Hilfe!

[Forumsgast]

Die Subquery selbst braucht ca. 50ms.

Gut, das war mir nicht klar.

Dass ich der Formulierung für das Select nicht "glaube" hab ich ja schon beschrieben. Das Remoteargument klingt sinnvoll, aber ist es wirklich so, dass sinnlos 20 Mio ds lokal geladen werden, um dann festzustellen, bleiben nur 20 übrig?
Hast Du mal einen simplen Join von "rows" und subquery ausprobiert?
Und wenn es in der Subquery nur ein simpler Join ist, dann ist das "Limit 1" tatsächlich das Konstrukt, um 1 DS für die (sortierte?) Ergebnismenge zu garantieren?
Mir ist nicht ganz klar, worauf welche Größenangaben treffen, die lokalen "rows", die Remotemenge, der Join in der Remotemenge, der ungefilterte Remote Join, ..

[Forumsgast]

Habe bis jetzt weniger mit der DB-Administration zu tun gehabt,

Danke für die Hilfe!

Man kann das auch outsourcen. Zum Beispiel an uns

[Forumsgast]

Ich versuch's mal verbal zu beschreiben, was da tatsächlich passieren soll: Es gibt Programme die hin und wieder gestartet werden (Jobs) und jeweils eine gewisse Anzahl an Rows bearbeiten (von extern). Nun kommen dort gelegentlich ein paar Zeilen dazu. Um beim nächsten Start des Programms nicht ALLE Zeilen nochmal bearbeiten zu müssen, möchte ich mit obigen Statement die ersten 20 Zeilen vom letzten Job laden. Das ist die Stelle an der ich nicht mehr weiterschauen muss, weil alles dahinter schon bearbeitet wurde. (Die Einträge sind chronologisch.)

Die Subquery holt zu einem gegebenen Programmnamen die Job-ID vom letzten Job der zu dem gegebenen Programm gehört.

Beim ersten Programmstart werden alle Zeilen verarbeitet, danach nur jeweils die neuen. Dementsprechend haben 95% der Jobs nur sehr wenige Zeilen, die anderen 5% uU. sehr viele.

Zur Übertragung: Das einzige was übertragen werden sollte ist der 1 Query + ein Ergebnis von 20 Rows. Die einfache Alternative wären einfach 2 Queries wo ich zuerst die Job ID abfrage und dann den obigen Query mit eingesetzter Job ID. Mir war/ist nur nicht klar, warum das so einen Unterschied macht.

[Forumsgast]

↵

, aber ist es wirklich so, dass sinnlos 20 Mio ds lokal geladen werden, um dann festzustellen, bleiben nur 20 übrig?

nein, die verlassen die DB nicht. Aber die DB hat X Buffers in shared_buffers und Y Buffers müssen von Platte gekratzt werden. Je Buffer 8KB. Kann man sich ausrechnen, was da an Mengen durchgeht. Das Problem, was hier sichtbar wird, hat man manchmal auch bei prepared statements - auch da sind zu Planungszeit die exakten Parameter nicht bekannt. Wenn der Planner diese kennt UND passende Statistiken hat, kann er die Ergebnissmenge besser abschätzen, und diese ist maßgeblich für die Wahl des Planes. Da ist die letzten Jahre schon viel passiert, z.B. in PG10 mit Statistiken über mehrere Spalten (Danke an meinen Kollegen Tomas Vondra aus Prag), und in PG11 mit partition pruning bei partitionierten Tabellen (Danke an meinen Kollegen Alvaro Herrera aus Chile). Aber es ist noch viel zu tun, um es noch besser zu bekommen...

[Forumsgast]

da, wo der Plan schlecht ist, wählt er einen konservativeren Plan, da er zur Plaungszeit das Resultat von "SELECT 6965 as f LIMIT 1" nicht kennt.

Hab ich als "User" eigentlich die Möglichkeit mir die verworfenen Pläne für ein Query anzeigen zulassen, zumindestens die Relevanten?

[Forumsgast]

Nein.

[Forumsgast]

Das einzige was übertragen werden sollte ist der 1 Query + ein Ergebnis von 20 Rows. Die einfache Alternative wären einfach 2 Queries wo ich zuerst die Job ID abfrage und dann den obigen Query mit eingesetzter Job ID. Mir war/ist nur nicht klar, warum das so einen Unterschied macht.

Wie gesagt, einfach mal einen normalen Join probieren -würde mich echt interessieren- oder einen conditional Index, den man auf dem Remoteserver aufsetzt und parallel zu diesem Job hier anpasst als Zwangsmaßnahme. Letzteres ist natürlich schon relativ aufwendig im Vergleich zu der Variante in 2 Schritten.

[Forumsgast]

Die Subquery steckt nur da drinnen, weil ich sonst zwei Queries bräuchte und die Abfrage auf einen Remote DB-Server gehen und ich daher möglichst wenig Queries/Netzwerkverkehr möchte.

Was hat die Anzahl der Queries mit dem Netzwerkverkehr zu tun? Man kann ja pro Request auch mehrere Queries ausführen. Für mich riecht das irgendwie nach skurriler Mikrooptimierung, die mehr Schaden anrichtet als sie bringt.

[Forumsgast]

@hellbringer: vielleicht steht der DB-Server auf der anderen Seite der Erdscheibe - oder gar auf dem Mond, Mars, $whatever, und Fragesteller will die Latenzen vermeiden.
@Tropi: man kann in einem Rutsch via CTE-Abfragen viele Dinge auf einmal machen, das als Hint.