Zu viele ORs?

Hallo,

ich stelle gerade eine PHP-Anwendung von mysql auf postgres um (denke trotzdem, dass meine Frage hier besser aufgehoben ist als in “Migration”). Dabei hab ich eine Query, die ungefähr so aussieht:

SELECT name FROM tabelle WHERE id IN (1,2,3,4,5,6)

Das Ganze funktioniert wunderbar, allerdings kann es sein, dass in dem IN-Statement sehr viele IDs stehen. Dabei hab ich festgestellt, dass die Abfrage bis 195 IDs schnell ist (ca. 50 ms). Sobald ich aber die 196. ID eintrage, wird das ganze richtig langsam (ca. 6 Sekunden).
Unter mysql kann ich da hunderte IDs hinschreiben und die Abfrage bleibt immer ungefähr gleich schnell (ca. 20 ms).

Hat jemand eine Idee, wodran das liegen kann? B-Tree-Index auf id ist gesetzt.

Bin kurz vorm verzweifeln und über jede Hilfe oder jeden Hinweis, wo ich weiter ansetzen kann, äußerst dankbar.

Bye Roogla

Edit: Die Tabelle hat ca. 250.000 Einträge unter pgsql und unter mysql > 1.000.000, da ich erstmal nur ein Teil der Daten portiert habe.

Nimm doch mal deine (fertige) Abfrage, also mit allen IDs drin, setz ein EXPLAIN ANALYZE davor und führe das auf der psql Shell aus.

Das Ergebnis schreibst du uns hier, bitte in CODE Tags setzen.

die Abfrage mit 196 IDs wirft das aus:

"Seq Scan on t_datasets_searchindices_rel  (cost=0.00..129053.50 rows=159277 width=4) (actual time=0.213..5590.435 rows=325 loops=1)
  Filter: ((searchindex_id = 2) OR (searchindex_id = 10) OR (searchindex_id = 11)[usw.])"
"Total runtime: 5591.567 ms"

und die Abfrage mit 195 IDs das:

"Bitmap Heap Scan on t_datasets_searchindices_rel  (cost=1843.81..127094.23 rows=158798 width=4) (actual time=3.611..5.774 rows=324 loops=1)"
"  Recheck Cond: ((searchindex_id = 2) OR (searchindex_id = 10) OR (searchindex_id = 11) OR (searchindex_id = 25) OR (searchindex_id = 26) OR (searchindex_id = 29) OR (searchindex_id = 30) OR (searchindex_id = 33) OR (searchindex_id = 34) OR (searchindex_id = 641) OR (searchindex_id = 642) OR (searchindex_id = 653) OR (searchindex_id = 654) OR (searchindex_id = 657) OR (searchindex_id = 658) OR (searchindex_id = 659) OR (searchindex_id = 660) OR (searchindex_id = 666) OR (searchindex_id = 667) OR (searchindex_id = 676) OR (searchindex_id = 677) OR (searchindex_id = 680) OR (searchindex_id = 681) OR (searchindex_id = 682) OR (searchindex_id = 683) OR (searchindex_id = 689) OR (searchindex_id = 690) OR (searchindex_id = 699) OR (searchindex_id = 700) OR (searchindex_id = 703) OR (searchindex_id = 704) OR (searchindex_id = 705) OR (searchindex_id = 706) OR (searchindex_id = 1405) OR (searchindex_id = 1406) OR (searchindex_id = 1417) OR (searchindex_id = 1418) OR (searchindex_id = 1421) OR (searchindex_id = 1422) OR (searchindex_id = 1423) OR (searchindex_id = 1424) OR (searchindex_id = 1433) OR (searchindex_id = 1434) OR (searchindex_id = 1443) OR (searchindex_id = 1444) OR (searchindex_id = 1447) OR (searchindex_id = 1448) OR (searchindex_id = 1449) OR (searchindex_id = 1450) OR (searchindex_id = 7539) OR (searchindex_id = 7540) OR (searchindex_id = 7553) OR (searchindex_id = 7554) OR (searchindex_id = 7563) OR (searchindex_id = 7564) OR (searchindex_id = 7566) OR (searchindex_id = 7567) OR (searchindex_id = 22546) OR (searchindex_id = 22547) OR (searchindex_id = 22561) OR (searchindex_id = 22562) OR (searchindex_id = 27200) OR (searchindex_id = 27201) OR (searchindex_id = 27210) OR (searchindex_id = 27211) OR (searchindex_id = 27214) OR (searchindex_id = 27215) OR (searchindex_id = 27216) OR (searchindex_id = 27217) OR (searchindex_id = 32563) OR (searchindex_id = 32564) OR (searchindex_id = 32585) OR (searchindex_id = 32586) OR (searchindex_id = 32589) OR (searchindex_id = 32590) OR (searchindex_id = 32593) OR (searchindex_id = 32594) OR (searchindex_id = 32597) OR (searchindex_id = 32598) OR (searchindex_id = 32599) OR (searchindex_id = 32600) OR (searchindex_id = 41271) OR (searchindex_id = 41272) OR (searchindex_id = 41281) OR (searchindex_id = 41282) OR (searchindex_id = 41283) OR (searchindex_id = 41284) OR (searchindex_id = 41285) OR (searchindex_id = 41286) OR (searchindex_id = 41893) OR (searchindex_id = 41894) OR (searchindex_id = 41907) OR (searchindex_id = 41908) OR (searchindex_id = 41914) OR (searchindex_id = 41915) OR (searchindex_id = 41916) OR (searchindex_id = 41917) OR (searchindex_id = 41929) OR (searchindex_id = 41930) OR (searchindex_id = 41943) OR (searchindex_id = 41944) OR (searchindex_id = 56905) OR (searchindex_id = 56906) OR (searchindex_id = 56919) OR (searchindex_id = 56920) OR (searchindex_id = 56923) OR (searchindex_id = 56924) OR (searchindex_id = 56925) OR (searchindex_id = 56926) OR (searchindex_id = 56927) OR (searchindex_id = 56928) OR (searchindex_id = 58491) OR (searchindex_id = 58492) OR (searchindex_id = 58519) OR (searchindex_id = 58520) OR (searchindex_id = 58523) OR (searchindex_id = 58524) OR (searchindex_id = 58525) OR (searchindex_id = 58526) OR (searchindex_id = 58527) OR (searchindex_id = 58528) OR (searchindex_id = 58529) OR (searchindex_id = 58530) OR (searchindex_id = 58531) OR (searchindex_id = 58532) OR (searchindex_id = 58533) OR (searchindex_id = 58534) OR (searchindex_id = 65359) OR (searchindex_id = 65360) OR (searchindex_id = 65379) OR (searchindex_id = 65380) OR (searchindex_id = 65383) OR (searchindex_id = 65384) OR (searchindex_id = 65387) OR (searchindex_id = 65388) OR (searchindex_id = 65391) OR (searchindex_id = 65392) OR (searchindex_id = 66452) OR (searchindex_id = 66453) OR (searchindex_id = 66464) OR (searchindex_id = 66465) OR (searchindex_id = 66466) OR (searchindex_id = 66467) OR (searchindex_id = 70089) OR (searchindex_id = 70090) OR (searchindex_id = 70103) OR (searchindex_id = 70104) OR (searchindex_id = 70107) OR (searchindex_id = 70108) OR (searchindex_id = 70109) OR (searchindex_id = 70110) OR (searchindex_id = 70111) OR (searchindex_id = 70112) OR (searchindex_id = 70194) OR (searchindex_id = 70195) OR (searchindex_id = 70212) OR (searchindex_id = 70213) OR (searchindex_id = 70226) OR (searchindex_id = 70227) OR (searchindex_id = 70230) OR (searchindex_id = 70231) OR (searchindex_id = 70234) OR (searchindex_id = 70235) OR (searchindex_id = 80216) OR (searchindex_id = 80219) OR (searchindex_id = 80220) OR (searchindex_id = 80231) OR (searchindex_id = 80232) OR (searchindex_id = 80233) OR (searchindex_id = 80234) OR (searchindex_id = 80235) OR (searchindex_id = 80236) OR (searchindex_id = 84513) OR (searchindex_id = 84514) OR (searchindex_id = 84526) OR (searchindex_id = 84527) OR (searchindex_id = 84530) OR (searchindex_id = 84531) OR (searchindex_id = 86045) OR (searchindex_id = 86046) OR (searchindex_id = 86062) OR (searchindex_id = 86063) OR (searchindex_id = 86070) OR (searchindex_id = 86071) OR (searchindex_id = 86072) OR (searchindex_id = 86073) OR (searchindex_id = 86786) OR (searchindex_id = 86787) OR (searchindex_id = 86799) OR (searchindex_id = 86800) OR (searchindex_id = 86801) OR (searchindex_id = 86802) OR (searchindex_id = 89530) OR (searchindex_id = 89531) OR (searchindex_id = 89552))"
"  ->  BitmapOr  (cost=1843.81..1843.81 rows=248230 width=0) (actual time=3.533..3.533 rows=0 loops=1)"
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.209..0.209 rows=44 loops=1)"
"              Index Cond: (searchindex_id = 2)"
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.029..0.029 rows=1 loops=1)"
"              Index Cond: (searchindex_id = 10)"
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.010..0.010 rows=1 loops=1)"
"              Index Cond: (searchindex_id = 11)"
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.042..0.042 rows=1 loops=1)"
"              Index Cond: (searchindex_id = 25)"
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.016..0.016 rows=1 loops=1)"
"              Index Cond: (searchindex_id = 26)"
[usw.]
"        ->  Bitmap Index Scan on searchindex_idx1  (cost=0.00..9.46 rows=1273 width=0) (actual time=0.009..0.009 rows=1 loops=1)"
"              Index Cond: (searchindex_id = 89552)"
"Total runtime: 7.839 ms"

da ich nicht mehr als 10.000 Zeichen posten darf, hab ich mal das, was sich immer wiederholt mit [usw.] ersetzt.

Bye Roogla

Vermutlich würde Dir hier ein Vergrößern von Parametern der Speicherverwaltung helfen (work_mem oder so, bin mir da jetzt nicht sicher). Aber damit verlagerst Du das Problem nur.

Hast Du mal die explain analyse - Ausgaben Dir angeschaut, wählt er da plötzlich eine grundlegend andere Strategie?

Kannst Du Deine id-Werte in eine andere (temp.) table werfen, oder hast sie gar schon in einer? Dann wäre ein regulärer JOIN vermutlich deutlich billiger → schneller.

Andreas

Hallo Andreas,

die Variante mit dem “work_mem” hab ich jetzt noch nicht versucht, aber das kommt auf jeden Fall noch dran. Natürlich hast du dabei Recht, dass das nicht wirklich die Lösung des Problems sein kann, unendlich Speicher steht hier leider nicht zur Verfügung :slight_smile:

Ansonsten hab ich natürlich das Problem stark vereinfacht, die Query ist noch um ein ganzes Stück komplexer, sieht also eher so aus:

SELECT 
 uid 
FROM 
 table1 inner join table2 ...
WHERE
((table1.id IN (1,2,3) and table2.id IN (4,5,6)) OR (table1.id IN (7,8,9) AND table2.id IN (10,11,12))

Und das ganze unter Umständen noch mit mehr als 2 Tabellen. und noch mehr OR-Verschaltungen.

Natürlich weiß ich, dass so viele ORs das ganze langsam machen (können), allerdings stehen in den Tabellen nur 2 Spalten mit IDs, weswegen eigentlich auch so viele ORs schnell sein müssten, dachte ich mir zumindest :slight_smile: Und mysql liefert ja auch den Beweis, dass es schnell geht.

Ich habe auch schon versucht alle Werte in eine temporäre Tabelle zuschreiben und dann Subselects statt der INs zu nehmen, was allerdings noch länger gedauert hat, wahrscheinlich deshalb, weil es dann schon 7 Subselects waren und damit wieder der Speicher an seine Grenzen stieß?!?

Wahrscheinlich werde ich nochmal versuchen, das ganze mit Joins umzuschreiben, wobei ich immer noch hoffe, dass es irgendeine Einstellung gibt, die das ganze dann auch so laufen läßt!

Zum Schluß sei nur nochmal gesagt, dass ich von postgreSQL sehr begeistert bin, auch wenn die Migration von mysql auf postgres sich teilweise doch als nicht so leicht herausgestellt hat, wie gehofft. Allerdings sind die Perfomancegewinne (abgesehen von dem hier angesprochenen Probleme) wirklich faszinierend, hätte ich nicht gedacht.

Bye Roogla

einen Seq-Scan

und die Abfrage mit 195 IDs das:

Bitmap Index Scan

Damit ist der Unterschied oder das Problem ab 196 erst mal klar, hatte ich so erwartet. Allerdings fällt mir grad keine gescheite Lösung ein.


Andreas

So. Ich hab in der Mailingliste pgsql-sql@postgresql.org das mal angesprochen.

Was ich übersehen hatte: die geschätzten 159277 rows stehen in krassen Widerspruch zu den tatsächlich anfallenden 325. Du hast offensichtlich sehr viele tote Tupel, lange kein VACUUM gemacht?
Wenn der Planner der Meinung ist, daß die Anzahl der Result-Rows nicht wirklich kleiner als die eigentliche Table ist, wählt er einen Seq-Scan, weil das dann natürlich preiswerter wäre.

Was Du auch machen kannst: (kam als Tipp in der ML): die Statistiken erhöhen für diese Spalte.
(ALTER TABLE … ALTER COLUMN … SET STATISTICS=N)


Der ganze Thread in der ML:

Andreas

Hallo Andreas,

erstmal vielen Dank fürs Fragen in der Mailinglist.
Ich habe direkt nach dem Anlegen der Tabelle (die wird einmal erzeugt und dann nicht mehr geändert) ein VACUUM gemacht. Das mit den Statistiken erhöhen hab ich jetzt noch nicht gemacht, werde ich nochmal testen.

Ich hab hier einen Performanceguide im Netz gefunden (hab gerade nicht die URL parat, weil die auf einem anderem Rechner ist, der bereits “ins Wochenende” gegangen ist :slight_smile: ) und benutze den auch immer nebenbei, hat mir auch schon gut geholfen.

Auf jeden Fall hab ich jetzt den Sequenz-Scan ausgeschaltet und schon läuft das ganze so, wie ich es gerne hätte. Zusätzlich hab ich den Code noch ein bißchen optimiert, so dass längst nicht mehr so viele IDs abgefragt werden müssen.
Allerdings mach ich das “Ausschalten” z.Z. so, dass ich vor der Query ein “set enable_seqscan=off” und nachdem Query ein “set enable_seqscan=on” mache. Gibts da eine einfachere Methode? Vielleicht sowas wie “SELECT NO_SEQSCAN ID FROM …”?

1000 Dank für die Hilfe bis hierhin. Bin beeindruckt von der Hilfsbereitschaft!!!

Bye Roogla

Mir nix bekannt, vermutlich nein.

1000 Dank für die Hilfe bis hierhin. Bin beeindruckt von der Hilfsbereitschaft!!!

Bye Roogla

Bitte. Es ist meine Form, mich bei den Machern von PG zu bedanken, hier zu helfen. PG ist ein erstklassiges Stück freier Software mit einer wirklich guten Community. :wink:


Andreas