warum wird mein index nicht verwendet?

Anwendungen & Erweiterungen Allgemeines
1

Ich fürchte, meine Frage ist alt:

Der Query
SELECT id,hostname FROM names WHERE domain_id=1 AND ipcount>0 LIMIT 1000;
verwendet keinen Index und ist entsprechend langsam.

Die Tabelle besteht im wesentlichen aus

Table names
id (serial)
hostname (varchar)
domain_id (integer, not null, default=0)
tld_id (integer, not null, default=0)
ipcount (integer, not null, default=0);

Auf allen Spalten liegt je ein Index, es gibt rund 40 Millionen Einträge, derzeit rund 5 Millionen (über die Tabelle verteilt), auf die obige Bedingung zutrifft. Wie oben beschrieben dauert der Query bis zu einigen Minuten…

Postgresql weigert sich standhaft, einen (oder zwei) index(e) für domain_id und ipcount zu verwenden. Schränke ich die Bedingung auf ein Feld ein, wird ein Index verwendet.
Ich habe einen kombinierten Index (domain_id, ipcount) versucht, ebenfalls ohne Erfolg.
Ich habe den Query geändert (geklammert) - ohne Erfolg.

PG (8.1.2) sagt:
EXPLAIN SELECT id,hostname FROM names WHERE domain_id=1 AND ipcount>0;
QUERY PLAN

Seq Scan on “names” (cost=0.00…1045608.23 rows=6547054 width=27)
Filter: ((domain_id = 1) AND (ipcount > 0))
(2 Zeilen)


Alles was mir jetzt noch einfällt, wäre, das in einen Subquery zu kapseln, damit pro Query nur ein Index verwendet wird - aber das wäre dann ja wie bei MySQL.

Setze ich ein “ORDER BY ID” dran, wird der entsprechende Index verwendet.
Setze ich ein “ORDER BY ipcount,domain_id” dran, bleibt es wiederrum ein sequential scan.

Ich bin mit meinem Latein am Ende.

[edit]
Falls das hilft:
domains=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE ‘names%’;
relname | relkind | reltuples | relpages
---------------±--------±------------±---------
names_id_seq | S | 1 | 1
names_ipcount | i | 4.17133e+07 | 95689
names | r | 4.16982e+07 | 419141
names_tld_id | i | 4.17177e+07 | 95738
names_pkey | i | 4.17113e+07 | 96908
(5 Zeilen)


Sehr verwunderlich finde ich dies:
domains=# SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = ‘names’;
attname | n_distinct | most_common_vals
------------±-----------±---------------------------------------------------------------------------------------------------------
id | -1 |
hostname | -1 |
ipcount | 12 | {1,0}
lastupdate | 52434 | {111,1156607575,1156611410,1156626237,1156640686,1156645606,1156658560,1156668110,1156674849,1156681689}
tld_id | 34 | {0}
domain_id | 2645 | {1,0,76689,45061,12,1772,113,3959,11670,22073}
(6 Zeilen)

Es gibt definitv nicht 2645 distinct domain_id’s, sondern rund 10.000.000 verschiedene. auch bei den distinct tlds sollte es rund 800 geben, nicht nur 24.

Kann es sein, dass die stats hier den Query-Planer auf eine falsche Fährte locken?
Kann ich das mit “VACUUM ANALYZE” aktualisieren?

Thomas


Thomas

2

5 von 40 ist relativ viel. Was passiert, wenn Du seq_scan expliziet verbietest?
(set enable_seqscan=false :wink:


Postgresql weigert sich standhaft, einen (oder zwei) index(e) für domain_id und ipcount zu verwenden. Schränke ich die Bedingung auf ein Feld ein, wird ein Index verwendet.
Ich habe einen kombinierten Index (domain_id, ipcount) versucht, ebenfalls ohne Erfolg.
Ich habe den Query geändert (geklammert) - ohne Erfolg.

PG (8.1.2) sagt:
EXPLAIN SELECT id,hostname FROM names WHERE domain_id=1 AND ipcount>0;
QUERY PLAN

Seq Scan on “names” (cost=0.00…1045608.23 rows=6547054 width=27)
Filter: ((domain_id = 1) AND (ipcount > 0))
(2 Zeilen)

Stimmen die Statistiken? Ein EXPLAIN ANALYSE wäre hilfreicher. Möglicherweise hast Du sehr viele tote Tupels.
Kann es sein, das die Bedingung ipcount>0 nicht sehr selektiv ist? Dann hilft ein Index nicht.
Der Planner schätzt 6,5 Millionen Zeilen, du sprichst von 5 Millionen

Alles was mir jetzt noch einfällt, wäre, das in einen Subquery zu kapseln, damit pro Query nur ein Index verwendet wird - aber das wäre dann ja wie bei MySQL.

g

Andreas

3

Ah. Ein Teilerfolg:

domains=# set enable_seqscan=false; EXPLAIN SELECT id,hostname FROM names WHERE ipcount>0 and domain_id=1 LIMIT 10000;
SET
QUERY PLAN

Limit (cost=0.00…165341.08 rows=10000 width=27)
-> Index Scan using names_ipcount on “names” (cost=0.00…108249695.19 rows=6547054 width=27)
Index Cond: (ipcount > 0)
Filter: (domain_id = 1)
(4 Zeilen)


Die Indizes sind alle BTREE-Indexe. Wäre ggf. für IPCount ein Hash-index hilfreicher? (ipcount hat aber nur ca. 10 verschiedene Werte).
Eigentlich sollte der Planer hier einen Bitmap-Scan vermelden, oder?

thomas

4

interessant vielleicht auch die absoluten zahlen für ipcount>1 und domain_id=1:

domains=# set enable_seqscan=false; EXPLAIN SELECT n1.id,n1.hostname FROM names as n1,names as n2 WHERE n1.ipcount>0 and n2.domain_id=1 LIMIT 1000;
SET
QUERY PLAN

Limit (cost=100000000.00…100003573.36 rows=1000 width=27)
-> Nested Loop (cost=100000000.00…976455936018021.25 rows=273260172206010 width=27)
-> Seq Scan on “names” n2 (cost=100000000.00…100941263.53 rows=9001470 width=0)
Filter: (domain_id = 1)
-> Index Scan using names_ipcount on “names” n1 (cost=0.00…108173801.99 rows=30357283 width=27)
Index Cond: (ipcount > 0)
(6 Zeilen)


Thomas

5

Fein. Aber kein Explain Analyse. Die Schätzungen des Planners und die reale Anzahl der Ergebnisszeilen liegen zwar nicht um Zehnerpotenzen auseinander, aber sie liegen auseinander, offensichtlich. Darum die Frage: Vacuum wird gemacht, oder? Ändern sich die Daten, oder kommen immer nur neue rein?

Die Indizes sind alle BTREE-Indexe. Wäre ggf. für IPCount ein Hash-index hilfreicher? (ipcount hat aber nur ca. 10 verschiedene Werte).

wenn, dann aber eher für domain_id. Ich habe aber bisher nur vernommen, daß Hash-Indexe besser seien.

Aus der Doku: “Hash indexes can only handle simple equality comparisons.”. Das machst Du aber bei ipcount nicht.


Eigentlich sollte der Planer hier einen Bitmap-Scan vermelden, oder?

Bitmap Index Scan, wenn 2 oder mehr Indexe genutzt werden.


Andreas

6

EXPLAIN ANALYZE dauert lang - deshalb hab ich das erstmal vermieden.
AutoVacuum ist in der .conf aktiviert - sollte da nicht auch ein analyze gemacht werden?

Ansonsten habe ich das Problem, dass ein VACUUM ANALYZE einige Stunden dauert, in der die DB quasi nicht benutzbar ist (100% Last auf der Festplatte).
Ich hab das heute Vormittag von Hand angestossen, es läuft noch. Vielleicht wird nachher alles besser?

Bezüglich der Festplattenlast: Ich habe letzte Woche ein Proliant Storagesystem mit 6 SCSI-Platten erstanden, das die Problematik der Festplattenperformance etwas lindern sollte - es dauert aber noch ein wenig, bis ich das produktiv da habe (dazu muss dann auch noch die DB auf einen anderen Rechner umziehen…)

Thomas

7

ja, is klar, das führt die Query ja auch aus. Es zeigt dann aber auch sehr exakt, was der Planner schätzt (als Grundlage für die Wahl seiner Strategie) und was real passiert.

AutoVacuum ist in der .conf aktiviert - sollte da nicht auch ein analyze gemacht werden?

Da bin ich mir, ehrlich gesagt, grad nicht sicher.
Ich glaube mich aber an eine Aussage von Tom Lane kürzlich erinnern zu können, wo er riet, ein vacuum full via CRON laufen zu lassen, falls man lastschwache Zeiten hat.

Ansonsten habe ich das Problem, dass ein VACUUM ANALYZE einige Stunden dauert, in der die DB quasi nicht benutzbar ist (100% Last auf der Festplatte).
Ich hab das heute Vormittag von Hand angestossen, es läuft noch. Vielleicht wird nachher alles besser?

Vielleicht.

Wir hatten im deutschen IRC-Channel vor wenigen Tagen jemanden, der hatte eine table, die irgendwas um die 50 GByte belegte. Ein ‘create new_table as select * from table’ erzeugte eine etwa 2,5 GByte große table. WTF? Ja, und das trotz auto-vacuum und so. Es half dann ein CLUSTER.

Bezüglich der Festplattenlast: Ich habe letzte Woche ein Proliant Storagesystem mit 6 SCSI-Platten erstanden, das die Problematik der Festplattenperformance etwas lindern sollte - es dauert aber noch ein wenig, bis ich das produktiv da habe (dazu muss dann auch noch die DB auf einen anderen Rechner umziehen…)

viel erfolg…

Andreas

8

Wir hatten im deutschen IRC-Channel vor wenigen Tagen jemanden, der hatte eine table, die irgendwas um die 50 GByte belegte. Ein ‘create new_table as select * from table’ erzeugte eine etwa 2,5 GByte große table. WTF? Ja, und das trotz auto-vacuum und so. Es half dann ein CLUSTER.
Andreas

Ähnliches kenne ich von Oracle. CLUSTER hat für mich ebenfalls den Nachteil, dass während des ReClusterns (und das wäre aufgrund der Vielzahl neuer Einträge regelmäßig nötig) die Tabelle gelockt ist.

Vacuum Analyze brachte nicht wirklich was.

EXPLAIN ANALYSE sagt:

domains=# EXPLAIN ANALYSE SELECT id,hostname from names where ipcount>0 and domain_id=1 LIMIT 1000;
QUERY PLAN

Limit (cost=0.00…9989.84 rows=1000 width=26) (actual time=12.345…121294.266
rows=1000 loops=1)
→ Index Scan using names_ipcount on “names” (cost=0.00…88991644.77 rows=8
908211 width=26) (actual time=12.341…121292.214 rows=1000 loops=1)
Index Cond: (ipcount > 0)
Filter: (domain_id = 1)
Total runtime: 121295.357 ms
(5 Zeilen)


121 sekunden… :confused:
Dabei lief keinerlei sonstiger Query auf der DB, und während des Query 100% Festplattenlast.

Im “Normalbetrieb” laufen viele Queries auf diese Tabelle parallel, dazu zwischen 10 und 100 INSERTS pro Minute in die Tabelle.

a) werden die Queries während des obigen Query langsamer
b) wird obiger Query im Normalbetrieb auch langsamer.

Seufz.

Thomas

9

Naja. Die Zeit verlierst Du bei dem Index-Scan, welcher names_ipcount nutzt. Offensichtlich ist die Bedingung ’ ipcount>0’ alles andere als selektiv → auch ein Index-Scan läuft auf quasi einen Seq-Scan hinaus.

Das Result liefert bald 9 Millionen Zeilen, aber Du willst nur 1000. Vielleicht würde es gewaltig was bringen, nach einer weiteren Spalte, die eine hohe Seletivität hat, zu filtern? Aber ich weiß nicht, für was Du das Result brauchst.
Also, vielleicht hast Du ja noch z.B. eine Datums-Spalte und selektierst einfach nur das, was die letzten N Tage aufgezeichnet wurde, oder sowas.

Aber as sind nun auch nur mehr oder minder Vermutungen meinerseits…

Andreas

10

Ja, so falsch liegst du da gar nicht.
Das Problem ist, dass die betroffenen Einträge ID-Technisch quasi mitten in der Tabelle sind.

Ich habe den Query mal eine Zeitlang gestoppt und der Tabelle viele Zeilen hinzugefügt. Damit sind viele Einträge am Ende der Tabelle. Dann ein “order by id desc” dazu und voila, 1000 zeilen in unter einer sekunde.

Jetzt sind alle Einträge am Ende der Tabelle abgearbeitet, und ich bin wieder da, wo ich mal war: die zeilen in der mitte der Tabelle sind schwer erreichbar.

Jetzt ist die Frage, wie ich diese Hosts mitten in der Tabelle möglichst schnell rauskriege.

Meine denke:

eine neue spalte “tmp (integer, default 0)” einfügen.
update names set tmp=1 where domain_id=1;
update names set tmp=(tmp+1) where ipcount>0;

aber am ende wird das dann auch nicht schneller?

hmmm.

Ich mach erstmal das, was auch der Oracle-Admin gerne tut: ich erstelle einen Dump (ca.6 GB), lösche den alten Krempel und spiele den Dump nochmal ein.

Thomas

11

Eine Frage: wie häufig ändern sich die Einträge in der Tabelle?

Probier mal einen VACUUM FULL ANALYZE für die Tabelle(n). Und führe den Query mal ohne das Limit aus, wie lange braucht der dann?

Ach, eine letzte Frage:
wieviele Einträge haben einen ipcount Wert von größer Null?

12

Das ist relativ. Du hast in Deiner Query kein ORDER, damit ist die Reihenfolge der Ergebnisse an sich egal.

Ich habe den Query mal eine Zeitlang gestoppt und der Tabelle viele Zeilen hinzugefügt. Damit sind viele Einträge am Ende der Tabelle. Dann ein “order by id desc” dazu und voila, 1000 zeilen in unter einer sekunde.

wäre dies nicht generell dann eine Lösung?

Jetzt sind alle Einträge am Ende der Tabelle abgearbeitet, und ich bin wieder da, wo ich mal war: die zeilen in der mitte der Tabelle sind schwer erreichbar.

Jetzt ist die Frage, wie ich diese Hosts mitten in der Tabelle möglichst schnell rauskriege.

Ich habe eben mit ads privat gequasselt, auch darüber. Er meinte:

“vavuum full analyze, und volles Explain. Und mal ohne Limit probieren.”


Andreas
(ads meldet sich wahrscheinlich auch gleich)

13

ok, also en detail:

CREATE TABLE “names” (
id serial NOT NULL,
hostname character varying(500) NOT NULL,
ipcount integer DEFAULT 0 NOT NULL,
lastupdate integer DEFAULT 0 NOT NULL,
tld_id integer DEFAULT 0,
domain_id integer DEFAULT 1 NOT NULL
);

auf allen feldern je ein index.

es finden zwischen 10 und 100 inserts pro minute statt:
insert into names (hostname) values(‘bla’);

es finden rund 50-150 updates pro minute statt
update names set ipcount=x,lastupdate=timestamp where id=y
update names set tld_id=a,domain_id=b,lastupdate=timestamp where id=x

dazu rund 1000 selects pro minute.

In Zukunft ist das auch so gedacht, dass der Query mit einem “ORDER BY ID DESC” läuft, damit hoffe ich die performanceprobleme zu beseitigen.
Im Moment gibts aber einen Overhang an einigen Millionen Einträgen, der eben abgearbeitet werden muss.

Ich hab über Nacht die ganze DB exportiert und dann neu aufbauen lassen - ohne gravierenden Erfolg, nichtmal kleiner geworden ist alles (spricht in meinen Augen FÜR pgsql).

ich hab jetzt den query aktualisiert:

my $query=“SELECT id,hostname FROM names WHERE
domain_id=1 AND ipcount=1 AND ID>$lastid order by ID ASC LIMIT $hcount”;

Das Script merkt sich $lastid (die zuletzt bearbeitete ID), um den SeqScan zumindest an der Stelle fortzuführen, wo der letzte aufhörte.

Mal sehen, ob diese Lösung praktikabel ist. Weiteres folgt - ich muss erstmal frühstücken.

Thomas

14

Ich fürchte, das die Datenbank der Meinung ist, das sie sowieso “ganz viele” Einträge durchsuchen muss und deshalb einen sequentiellen Scan vornimmt.

Der Planer bekommt irgendwann mit, das “> 0” als Beispiel soviele Einträge betrifft, das es sich nicht lohnt, einen Index anzusprechen, da er hinterher sowieso all diese Einträge aus der DB holen muss.

Dazu gehört allerdings auch ein sauberes VACUUM (FULL) ANALYZE, um die Tabellenstatistiken auf den neuesten Stand zu halten.

15

prima - der oben beschriebene query ist superfix und taugt dazu, die einträge in der mitte der tabelle zu fixen!

erfordert zwar ein script das die einträge von unten (order by id asc) abarbeitet, das macht aber kein problem.

ganz zufrieden macht mich das zwar nicht (warum geht das nicht schneller?), aber es ist definitv eine gut praktikable und performante lösung!

danke!

thomas

16

Deine erste Lösung mit dem LIMIT holt zuerst 4 Millionen Zeilen und wirft dann 3999000 wieder weg. Dazu wühlt es die ganze Tabelle um, da es ja fast, naja, nicht ganz, die komplette table erst mal liefern muß. Fullscan.

Deine neue Lösung holt nur das, was du einklich brauchst und muß daher nix wegwerfen.
Und es kann besser Indexe nutzen. Sie ist intelligenter, und damit schneller.


Andreas