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