mein query is zu langsam (die x.te)

Mein Query dauert zu lang.

Gegeben eine einfache Tabelle, produktiv mit rund 150.000.000 Zeilen:

ip-nummer (inet)
hostname (varchar)
host_id (int)
domain_id (int)

CREATE TABLE helper_ip_hostname
(
ip inet,
hostname character varying(500),
host_id integer NOT NULL,
domain_id integer NOT NULL DEFAULT 0
)
WITHOUT OIDS;

CREATE INDEX idx_helper_ip_hostname_ip_clustered
ON helper_ip_hostname
USING btree (ip);
ALTER TABLE helper_ip_hostname CLUSTER ON idx_helper_ip_hostname_ip_clustered;

Die Tabelle kriegt etwa 10 Inserts/Updates pro Sekunde, das Clustering ist also schlicht hinfällig, weil eine Reindizierung doch recht lang dauert.

Eine übliche Abfrage sieht so aus:

select hostname from helper_ip_hostname where ip = ‘82.98.86.167’ order by hostname asc
limit 100

ohne “order by” alles kein problem und sehr fix. bei weniger als 200 ergebnissen auch kein problem und unter 2 sekunden.

problematisch wirds bei ips mit vielen ergebnissen.

explain analyze select hostname from helper_ip_hostname where ip = ‘82.98.86.167’ order by hostname asc

“Sort (cost=31582.14…31603.26 rows=8445 width=24) (actual time=98344.601…98384.924 rows=20452 loops=1)”
" Sort Key: hostname"
" -> Bitmap Heap Scan on helper_ip_hostname (cost=229.04…31031.37 rows=8445 width=24) (actual time=1110.698…98237.524 rows=20452 loops=1)"
" Recheck Cond: (ip = ‘82.98.86.167’::inet)"
" -> Bitmap Index Scan on idx_helper_ip_hostname_ip_clustered (cost=0.00…226.93 rows=8445 width=0) (actual time=1091.353…1091.353 rows=20452 loops=1)"
" Index Cond: (ip = ‘82.98.86.167’::inet)"
“Total runtime: 98440.169 ms”

ich verstehe ja, dass dank des sort im query erstmal alle 20.452 ergebnisse geladen werden müssen - aber ist das wirklich normal, dass das so lange dauert?

kann ich ausser einem regelmässigen clustern des index irgendwas einfaches tun, damit derartige abfragen schneller werden?


thomas

Ich habe ein ähnliches Problem letztens erlebt und die Ursache (muss aber nicht mit deiner übereinstimmen) lag darin, das die Daten zu schnell gelesen und geschrieben wurden.

Im konkreten Fall war dies eine ca. 3 GB große Tabelle mit Postings und es werden immer die letzten 10 gesucht. Während die Datenbank nun Teile der Tabelle in den Speicher geladen hat, haben andere Anfragen andere Teile gebraucht, so das ständig I/O Last vorhanden war, nur um diverse Blöcke von der Platte zu lesen. Das wurde so schlimm, das die Queries teilweise in einen gesetzten Timeout von 30 Sekunden gelaufen sind … führt man einen einzelnen Query alleine aus, war der in einigen Millisekunden fertig.

Zu beobachten war eine sehr hohe Zahl von Blockzugriffen in den Statistiktabellen, dafür müssen row und block Statistiken aktiviert werden.

Abhilfe: mehr Speicher, viel mehr Speicher …

Jaaa. Speicher hätte ich - aber Postgres mag ihn nicht nehmen:
http://www.pg-forum.de/konfiguration/2072-pg-auf-windows-nimmt-zuwenig-speicher.html#post10437

Als Storage hängt ein RAID0 mit 8 SCSI-Serverplatten dran - der ist zwar in der reinen Übertragungsgeschwindigkeit recht langsam, skaliert aber ungemein, wenns um viele gleichzeitige Zugriffe geht.

Der Query läuft auch nicht übermässig viel schneller, wenn ich die Backendprozesse und den Webserver wegnehme.

tom