Seltsame Query-Vorschau/Ausführung

Hallo,
ich habe bei einer eigentlich einfachen Abfrage eine äußerst seltsame
Erscheinung:

Ich habe zwei Tabellen:
eine Adress-Tabelle (ca. 125.000 Sätze)
> CREATE TABLE as400.ass_adresse
> (
> adressnr_pk int4 NOT NULL,
> anrede_fk int2,
> titel varchar(20),
> name varchar(61),
> strasse varchar(50),
> lkz varchar(3),
> plz varchar(6),
> ort varchar(50),
> -- und eine Reihe weiterer normaler Felder
> );
> ALTER TABLE as400.ass_adresse
> ADD CONSTRAINT ass_adresse_pkey PRIMARY KEY (adressnr_pk);
>
> CREATE INDEX ass_adresse_plz ON ass_adresse USING btree (plz);
und eine Tabelle mit Suchworten (ca. 650.000 Sätze)
> CREATE TABLE as400.adrwords
> (
> type_pk int2 NOT NULL,
> adressnr_pk int4 NOT NULL,
> word_pk varchar(50) NOT NULL
> );
> ALTER TABLE as400.adrwords
> ADD CONSTRAINT adrwords_pkey PRIMARY KEY (type_pk, adressnr_pk,
> word_pk);
>
> CREATE INDEX ass_adrwords_word ON adrwords USING btree (type_pk, word_pk);
in der einzelne (normalisierte) Worte aus den Felder Name, Straße und
Ort der Adressen der ersten Tabelle stehen.

Meine Abfrage lautet jetzt
> select a.adressnr_pk,
> COALESCE(a.name, '') AS name,
> COALESCE(a.strasse, '') AS strasse,
> COALESCE(a.plz, '') AS plz,
> COALESCE(a.ort, '') AS ort
> from as400.ass_adresse a
> where a.plz = '47589'
> OR a.adressnr_pk in (
> select adressnr_pk
> from as400.adrwords
> where type_pk = 1 AND word_pk IN ('goertz', 'haftungsbeschraenkt',
> 'pflegeteam')
> OR type_pk = 2 AND word_pk IN ('kervenheimer')
> OR type_pk = 3 AND word_pk IN ('uedem'))
> ORDER BY adressnr_pk;
d.h. ich habe zwei mit OR verknüpfte Teilbedingungen, die darauf
hinauslaufen, dass alle Sätze eingeschlossen werden, bei denen entweder
die PLZ stimmt oder in Name, Straße oder Ort ein übereinstimmender
Suchbegriff gefunden wird.

Obwohl nur gut 40 Sätze als Ergebnis herauskommen und alles Relevante
indiziert ist, dauert die Query recht lange (mehrere Sekunden). Ein
EXPLAIN ANALYZE zeigt auch warum:
> Sort (cost=37024.87..37181.79 rows=62766 width=60) (actual
> time=957.211..957.308 rows=46 loops=1)
> Sort Key: a.adressnr_pk
> Sort Method: quicksort Memory: 22kB
> -> Seq Scan on ass_adresse a (cost=790.64..29769.14 rows=62766
> width=60) (actual time=47.745..956.781 rows=46 loops=1)
> Filter: (((plz)::text = '47589'::text) OR (hashed subplan))
> SubPlan
> -> Bitmap Heap Scan on adrwords (cost=55.40..790.11
> rows=212 width=4) (actual time=0.801..2.533 rows=46 loops=1)
> Recheck Cond: (((type_pk = 1) AND ((word_pk)::text =
> ANY ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[]))) OR ((type_pk
> = 2) AND ((word_pk)::text = 'kervenheimer'::text)) OR ((type_pk = 3)
> AND ((word_pk)::text = 'uedem'::text)))
> -> BitmapOr (cost=55.40..55.40 rows=212 width=0)
> (actual time=0.745..0.745 rows=0 loops=1)
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..39.75 rows=170 width=0) (actual time=0.505..0.505 rows=38
> loops=1)
> Index Cond: ((type_pk = 1) AND
> ((word_pk)::text = ANY
> ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[])))
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..9.78 rows=25 width=0) (actual time=0.105..0.105 rows=0
> loops=1)
> Index Cond: ((type_pk = 2) AND
> ((word_pk)::text = 'kervenheimer'::text))
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..5.71 rows=18 width=0) (actual time=0.119..0.119 rows=8
> loops=1)
> Index Cond: ((type_pk = 3) AND
> ((word_pk)::text = 'uedem'::text))
> Total runtime: 957.668 ms

Der Analyzer ist der irrigen Meinung, es kämen mehr als 60.000 Sätze aus
der Query heraus; damit ist natürlich ein sequenzieller Scan schneller
als ein indizierter Zugriff.

Komisch ist nur, dass die beiden Teile des ORs getrennt
nur PLZ:
> select a.adressnr_pk,
> COALESCE(a.name, '') AS name,
> COALESCE(a.strasse, '') AS strasse,
> COALESCE(a.plz, '') AS plz,
> COALESCE(a.ort, '') AS ort, r.rechtsform_txt
> from as400.ass_adresse a
> where a.plz = '47589'
> ORDER BY adressnr_pk;
EXPLAIN ANALYZE:
> Sort (cost=134.62..134.70 rows=32 width=60) (actual
> time=97.428..97.444 rows=8 loops=1)
> Sort Key: adressnr_pk
> Sort Method: quicksort Memory: 17kB
> -> Bitmap Heap Scan on ass_adresse a (cost=8.72..133.82 rows=32
> width=60) (actual time=97.177..97.380 rows=8 loops=1)
> Recheck Cond: ((plz)::text = '47589'::text)
> -> Bitmap Index Scan on ass_adresse_plz (cost=0.00..8.71
> rows=32 width=0) (actual time=97.068..97.068 rows=8 loops=1)
> Index Cond: ((plz)::text = '47589'::text)
> Total runtime: 97.531 ms
und nur restliche Daten:
>> select a.adressnr_pk,
>> COALESCE(a.name, '') AS name,
>> COALESCE(a.strasse, '') AS strasse,
>> COALESCE(a.plz, '') AS plz,
>> COALESCE(a.ort, '') AS ort
>> from as400.ass_adresse a
>> where a.adressnr_pk in (
>> select adressnr_pk
>> from as400.adrwords
>> where type_pk = 1 AND word_pk IN ('goertz',
>> 'haftungsbeschraenkt', 'pflegeteam')
>> OR type_pk = 2 AND word_pk IN ('kervenheimer')
>> OR type_pk = 3 AND word_pk IN ('uedem'))
>> ORDER BY adressnr_pk;
EXPLAIN ANALYZE:
> Sort (cost=940.64..940.69 rows=18 width=60) (actual
> time=466.769..466.864 rows=46 loops=1)
> Sort Key: a.adressnr_pk
> Sort Method: quicksort Memory: 22kB
> -> Nested Loop (cost=790.64..940.26 rows=18 width=60) (actual
> time=47.404..466.351 rows=46 loops=1)
> -> HashAggregate (cost=790.64..790.82 rows=18 width=4)
> (actual time=3.447..3.635 rows=46 loops=1)
> -> Bitmap Heap Scan on adrwords (cost=55.40..790.11
> rows=212 width=4) (actual time=1.231..3.260 rows=46 loops=1)
> Recheck Cond: (((type_pk = 1) AND ((word_pk)::text
> = ANY ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[]))) OR
> ((type_pk = 2) AND ((word_pk)::text = 'kervenheimer'::text)) OR
> ((type_pk = 3) AND ((word_pk)::text = 'uedem'::text)))
> -> BitmapOr (cost=55.40..55.40 rows=212 width=0)
> (actual time=1.126..1.126 rows=0 loops=1)
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..39.75 rows=170 width=0) (actual time=0.753..0.753 rows=38
> loops=1)
> Index Cond: ((type_pk = 1) AND
> ((word_pk)::text = ANY
> ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[])))
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..9.78 rows=25 width=0) (actual time=0.174..0.174 rows=0
> loops=1)
> Index Cond: ((type_pk = 2) AND
> ((word_pk)::text = 'kervenheimer'::text))
> -> Bitmap Index Scan on ass_adrwords_word
> (cost=0.00..5.71 rows=18 width=0) (actual time=0.184..0.184 rows=8
> loops=1)
> Index Cond: ((type_pk = 3) AND
> ((word_pk)::text = 'uedem'::text))
> -> Index Scan using ass_adresse_pkey on ass_adresse a
> (cost=0.00..8.29 rows=1 width=60) (actual time=9.801..10.040 rows=1
> loops=46)
> Index Cond: (a.adressnr_pk = adrwords.adressnr_pk)
> Total runtime: 467.172 ms
lediglich 32 Sätze bzw. 18 Sätze prognostizieren. Die Anzahl der
Ergebnissätze eines OR kann aber nicht größer sein, als die Summe der
beiden Teilbedingungen.

Hat der Postgres-Analyzer hier vielleicht eine kleine Macke?

Wenn ich die vollständige Query als
> select a.adressnr_pk,
> COALESCE(a.name, '') AS name,
> COALESCE(a.strasse, '') AS strasse,
> COALESCE(a.plz, '') AS plz,
> COALESCE(a.ort, '') AS ort
> from as400.ass_adresse a
> where a.adressnr_pk in (
> select adressnr_pk
> from as400.adrwords
> where type_pk = 1 AND word_pk IN ('goertz', 'haftungsbeschraenkt',
> 'pflegeteam')
> OR type_pk = 2 AND word_pk IN ('kervenheimer')
> OR type_pk = 3 AND word_pk IN ('uedem')
> union
> select adressnr_pk
> from ass_adresse
> where plz = '47589')
> ORDER BY adressnr_pk;
schreibe, ist das Ergebnis
> Nested Loop (cost=936.05..2957.09 rows=244 width=60) (actual
> time=2.501..6.672 rows=46 loops=1)
> -> Unique (cost=936.05..937.27 rows=244 width=4) (actual
> time=2.417..2.745 rows=46 loops=1)
> -> Sort (cost=936.05..936.66 rows=244 width=4) (actual
> time=2.412..2.520 rows=54 loops=1)
> Sort Key: adrwords.adressnr_pk
> Sort Method: quicksort Memory: 18kB
> -> Append (cost=55.40..926.37 rows=244 width=4)
> (actual time=0.617..2.247 rows=54 loops=1)
> -> Bitmap Heap Scan on adrwords
> (cost=55.40..790.11 rows=212 width=4) (actual time=0.613..1.703
> rows=46 loops=1)
> Recheck Cond: (((type_pk = 1) AND
> ((word_pk)::text = ANY
> ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[]))) OR ((type_pk =
> 2) AND ((word_pk)::text = 'kervenheimer'::text)) OR ((type_pk = 3) AND
> ((word_pk)::text = 'uedem'::text)))
> -> BitmapOr (cost=55.40..55.40 rows=212
> width=0) (actual time=0.569..0.569 rows=0 loops=1)
> -> Bitmap Index Scan on
> ass_adrwords_word (cost=0.00..39.75 rows=170 width=0) (actual
> time=0.369..0.369 rows=38 loops=1)
> Index Cond: ((type_pk = 1) AND
> ((word_pk)::text = ANY
> ('{goertz,haftungsbeschraenkt,pflegeteam}'::text[])))
> -> Bitmap Index Scan on
> ass_adrwords_word (cost=0.00..9.78 rows=25 width=0) (actual
> time=0.114..0.114 rows=0 loops=1)
> Index Cond: ((type_pk = 2) AND
> ((word_pk)::text = 'kervenheimer'::text))
> -> Bitmap Index Scan on
> ass_adrwords_word (cost=0.00..5.71 rows=18 width=0) (actual
> time=0.075..0.075 rows=8 loops=1)
> Index Cond: ((type_pk = 3) AND
> ((word_pk)::text = 'uedem'::text))
> -> Bitmap Heap Scan on ass_adresse
> (cost=8.72..133.82 rows=32 width=4) (actual time=0.145..0.319 rows=8
> loops=1)
> Recheck Cond: ((plz)::text = '47589'::text)
> -> Bitmap Index Scan on ass_adresse_plz
> (cost=0.00..8.71 rows=32 width=0) (actual time=0.113..0.113 rows=8
> loops=1)
> Index Cond: ((plz)::text = '47589'::text)
> -> Index Scan using ass_adresse_pkey on ass_adresse a
> (cost=0.00..8.26 rows=1 width=60) (actual time=0.069..0.073 rows=1
> loops=46)
> Index Cond: (a.adressnr_pk = adrwords.adressnr_pk)
> Total runtime: 6.968 ms
mit geplanten 244 Zeilen und gut 5 Millisekunden Ausführungszeit
deutlich dichter an der Wahrheit.

Habe ich hier irgendwo einen Denkfehler oder Postgres einen Analyse-Fehler?

mfg Volker

PS:
- Die Beispiele stammen von einer Postgres 8.3.9 (Linux, i386) sind aber
auch auf einer 8.4.4 mit annähernd gleichen Daten reproduzierbar
(die einzelnen Schritte des EXPLAIN unterscheiden sich ein wenig,
aber die viel zu hohe erwartete Satzanzahl, die sequenzielle Suche
und die damit verbundene lange Ausführungszeit bleiben).

- Ich habe schon früher einmal ein ähnliches Beispiel gehabt, in
dem ich ein 'feld = 5 or feld = 7 or feld = 23' in ein
'feld in (5, 7, 23)' umwandelte und dadurch die Ausführungszeit
deutlich verkürzte; auch wieder mit einer viel zu hohen
erwarteten Satzanzahl, die einen sequeziellen Scan verursachte.

- Das die Ausführungszeit des Analyzer-Statements mit 957 ms deutlich
unter der von mir genannten Gesamt-Ausführungszeit von mehr als fünf
Sekunden liegt, kommt wohl von Caching-Effekten. Jedenfalls egal ob
1 oder 5 Sekunden: Die Analyse des Ursprungs-Statements ist falsch.

--
Volker Böhm Tel.: +49 4141 981152 www.vboehm.de
Voßkuhl 5 Fax: +49 4141 981154
D-21682 Stade mailto:volker(at)vboehm(dot)de