abfragen auf tabelle langsam

Hallo,

ich habe zwei Tabellen in der gleichen Datenbank,
in der einen Tabelle sind ca. 6.5 Mio Einträge, in der anderen ca 4 Mio.
Die Spalten und Indizes sind gleich. Die eine Tabelle enthält lediglich noch doppelte Einträge.

Abfragen auf der größeren Tabelle gehen wesentlich schneller als Abfragen auf der kleineren. Was kann das sein ?

hier noch ein queryplan von select count(*), was mich verwundert ist dass,bei der langsamen tabelle das erste rows=9849297 ist

   "Aggregate (cost=1158793.22..1158793.22 rows=1 width=0) (actual time=154102.781..154102.782 rows=1 loops=1)"
" -> Seq Scan on ways_final (cost=0.00..1134169.97 rows=9849297 width=0) (actual time=10.113..152290.962 rows=5092181 loops=1)"
"Total runtime: 154126.262 ms"


"Aggregate (cost=495313.94..495313.95 rows=1 width=0) (actual time=55637.076..55637.077 rows=1 loops=1)"
" -> Seq Scan on ways_broken (cost=0.00..479291.75 rows=6408875 width=0) (actual time=18.210..53426.921 rows=6408875 loops=1)"
"Total runtime: 55641.470 ms"

Die Fehl-Schätzung deuted auf schlecht gepflegte Statistiken hin. Mache mal ein analyse auf diese Tabelle. Die Tabelle scheint auch jede Menge toter Tupel zu enthalten → vacuum anwenden.

Generell sind count(*) - Abfragen ohne WHERE langsam, da sie einen Seq-Scan nach sich ziehen. Im übrigen hat die eine Tabelle nicht ca. 4, sondern über 5 Millionen Einträge.


Andreas

hi, hier der output von vacuum und analyze

INFO:  vacuume »public.ways_final«
INFO:  Index »ways_final_pkey1« enthält 5092181 Zeilenversionen in 43223 Seiten
DETAIL:  0 Indexzeilenversionen wurde entfernt.
355 Indexseiten wurden gelöscht, 355 sind gegenwärtig wiederverwendbar.
CPU 0.21s/0.06u sec elapsed 7.50 sec.
INFO:  Index »source_idxf« enthält 5092181 Zeilenversionen in 38427 Seiten
DETAIL:  0 Indexzeilenversionen wurde entfernt.
17 Indexseiten wurden gelöscht, 17 sind gegenwärtig wiederverwendbar.
CPU 0.19s/0.05u sec elapsed 4.72 sec.
INFO:  Index »target_idxf« enthält 5092181 Zeilenversionen in 38581 Seiten
DETAIL:  0 Indexzeilenversionen wurde entfernt.
14 Indexseiten wurden gelöscht, 14 sind gegenwärtig wiederverwendbar.
CPU 0.21s/0.08u sec elapsed 4.39 sec.
INFO:  Index »geom_idxf« enthält 5092181 Zeilenversionen in 100178 Seiten
DETAIL:  0 Indexzeilenversionen wurde entfernt.
0 Indexseiten wurden gelöscht, 0 sind gegenwärtig wiederverwendbar.
CPU 0.50s/0.18u sec elapsed 10.89 sec.INFO:  »ways_final«: 0 entfernbare, 5092181 nicht entfernbare Zeilenversionen in 1035677 Seiten gefunden
DETAIL:  0 tote Zeilenversionen können noch nicht entfernt werden.
Es gibt 10980544 unbenutzte Itemzeiger.
859463 Seiten enthalten nützlichen freien Platz.
0 Seiten sind vollkommen leer.
CPU 7.29s/2.68u sec elapsed 151.51 sec.
WARNUNG:  Relation »public.ways_final« enthält mehr als »max_fsm_pages« Seiten mit nutzbarem freiem Platz
HINT:  Consider using VACUUM FULL on this relation or increasing the configuration parameter "max_fsm_pages".INFO:  vacuume »pg_toast.pg_toast_34678«
INFO:  Index »pg_toast_34678_index« enthält 1156 Zeilenversionen in 11 Seiten
DETAIL:  0 Indexzeilenversionen wurde entfernt.
0 Indexseiten wurden gelöscht, 0 sind gegenwärtig wiederverwendbar.
CPU 0.00s/0.00u sec elapsed 0.01 sec.INFO:  »pg_toast_34678«: 0 entfernbare, 1156 nicht entfernbare Zeilenversionen in 372 Seiten gefunden
DETAIL:  0 tote Zeilenversionen können noch nicht entfernt werden.
Es gibt 2072 unbenutzte Itemzeiger.
362 Seiten enthalten nützlichen freien Platz.
0 Seiten sind vollkommen leer.
CPU 0.00s/0.00u sec elapsed 0.11 sec.
Abfrage war erfolgreich nach 151766 ms. Keine Zeilen geliefert.




INFO:  analysiere »public.ways_final«
INFO:  »ways_final«: 3000 von 1035677 Seiten gelesen, enthalten 14711 lebende Zeilen und 0 tote Zeilen; 3000 Zeilen in Stichprobe, schätzungsweise 5078615 Zeilen insgesamt

Abfrage war erfolgreich nach 10609 ms. Keine Zeilen geliefert.

hat keine verbesserung gebracht

Da steht doch schon mal etwas, befolg den Hinweis bitte mal.
Also nicht das VACUUM FULL, sondern die Konfiguration anpassen.


hat keine verbesserung gebracht

Inwieweit ist die Datenbank konfiguriert?

Lass mal bitte ein

VACUUM ANALYZE VERBOSE;

laufen und paste die letzten paar Zeilen hier.

Ich hab mal das wichtige stehen gelassen.

Andreas

Hier die letzten Zeilen von
VACUUM ANALYZE VERBOSE;


INFO:  Free-Space-Map enthält 123138 Seiten in 21 Relationen
DETAIL:  Es sind insgesamt 153600 Page-Slots in Benutzung (einschließlich Overhead).
890592 Page-Slots werden benötigt, um den gesamten freien Platz verwalten zu können.
Aktuelle Begrenzungen sind:  153600 Page-Slots, 1000 Relationen, ergibt 965 kB Speicherverbrauch.
HINWEIS:  Anzahl der benötigten Page-Slots (890592) überschreitet max_fsm_pages (153600)
HINT:  Erhöhen Sie eventuell den Konfigurationsparameter »max_fsm_pages« auf über 890592.
Abfrage war erfolgreich nach 756438 ms. Keine Zeilen geliefert.



Inwieweit ist die Datenbank konfiguriert?

shared_buffers = 1024MB
effective_cache_size=1024MB

und jetzt noch
max_fsm_pages = 894000

keine verbesserung, nur die andere tabelle, die schon schnell war ist jetzt noch 10 000 ms schneller

Hast Du den Server neu gestartet? (ich weiß grad nicht, ob auch Reload ausreicht) und das Vacuum wiederholt?

Andreas

Es sieht mir so aus, als ob du bloß an den Systemen herumdoktorst.
Inwieweit kennst du dich mit dem Optimieren von PostgreSQL-Datenbanken aus?

was anderes als rumdoktorn geht doch sowieso nicht …

die allermeisten parameter muss man schaetzen - berechnungsvorschriften gibt es nur sehr wenige

mir faellt hier nur auf, dass am anfang von select count(*) gesprochen wurde … ein problem habt ihr entdeckt … und jetzt sind wir wieder bei unscharfen aussagen wie “langsam” oder “schnell” und niemand fragt mehr was denn nun “langsam” oder “schnell” ist

Wie du so schön sagst: es gibt keine Berechnungsvorschriften.

Einfach weil zu viele Parameter eine Rolle spielen - sowohl in der Konfiguration der Datenbank wie auch des Betriebssystems, der Hardware, der Datenbasis, der Anzahl und Art der Zugriffe und und und …

Wenn der “SELECT COUNT(*)” der einzige Anwendungsfall ist (was ich nicht glaube), kann man den wegoptimieren. Ob man damit den Rest der Probleme erschlägt, sei dahingestellt. Bislang kennen wir ein konkretes Problem und 4 Zeilen aus der Konfiguration, mehr nicht. Was soll man dazu noch groß sagen?

ich wollte nur deinen vorwurf entkräften … weil “optimierung” immer irgendwas von rumdoktoren hat :wink:

Wenn der “SELECT COUNT(*)” der einzige Anwendungsfall ist (was ich nicht glaube),

ich glaube das auch nicht - daher die implizite geheime frage … an den OP … WAS GENAU ist denn “langsam” … weil tabellen sind niemals langsam - nur Opertionen z.B. Abfragen … also WELCHE

mein konkretes problem war dass auf diesen tabellen ein routingalgorithmus (pgrouting) läuft, der war viel langsamer als auf meiner tabelle, die ich vorher hatte, die aber doppelte wege enthält…dann habe ich gemerkt dass die tabelle bei allen anfragen langsamer ist und nur noch mit count(*) getestet.

nach dem restart und erneutem vacuum keine änderung.

http://www.pg-forum.de/sql/3882-abfragen-auf-tabelle-langsam.html#post20983

ach du … pfft :smiley:

count() ist eine realitätsferne Abfrage - meist zumindest. Es macht auch exakt keinerlei Sinn, sich einen Plan von select count() from table; anzuschauen, wenn andere Abfragen als diese zu beschleunigen sind.

Andreas

ich wollte auch nicht den count(*) optimieren. nur wenn der auf ner tabelle mit weniger von den gleichen einträgen 3 mal so lang braucht stimmt doch allgmein mit der tabelle irgendwas nicht

Möge richtig sein, das kann aber ein halbes Dutzend Ursachen haben.

vorrausgesetzt, dass die beiden tables WIRKLICH strukturell gleich sind, auf dem gleichen system in der gleichen db (also mit der gleichen config) laufen und auch vergleichbare daten enthalten, kann nur daran liegen (<-- randbedingung), dass du

  1. immernoch kein VACUUM FULL ausgefuehrt hast oder
  2. die datenbank stark fragmentiert ist - auf dateisystemebene …

schaetzungsweise ist die “kleine table” sehr lang in benutzung … und wurde auch oft geschrieben … -> gescheites vacuum noetig

die andere grosse ist wahrscheinlich frisch erzeugt wurden

wenn du auf unterschiedlichen systemen/betriebsystemen rumprobierst (was wir nicht hoffen) hat das alles keinen wert

ja, die sind auf dem selben system in der selben db.

Es ist so dass die große Tabelle älter ist als die andere.

die neue Tabelle ist mit einem select into entstanden (von noch einer anderen tabelle), danach hab ich den primary key und indizes gesetzt.
dann hab ich dublicates entfernt mit

DELETE FROM ways_final WHERE gid IN (SELECT b.gid FROM ways_final a,  
ways_final b
   WHERE a.the_geom ~= b.the_geom AND a.gid > b.gid);

lasse jetzt gerade das vacuum full laufen, hatte eben nur vacuum auf die tabelle gemacht

wie ads und akretschmer dir bereits in ungewöhnlich diplomatischer weise nahe zu legen versuchten, solltest du das bereits vor geraumer zeit getan haben :wink: … weil du hast den wink mit dem zaunspfahl selbst gepostet → output von irgendeinem vacuum verbose

danach wäre interessant dass du die (zwar wenig relevanten) tests nochmal probierst … so ein kompletter table-scan sagt ja auch was über den zustand der tabelle aus