hey i have the following table def (834.000 rows, vaccum analyze'd):
dl_online=# \d mitglied Table "mitglied" Attribute | Type | Modifier
----------------+--------------+----------------------------mitgliedid | bigint | not nulldlnummer |
varchar(30) |vorname | varchar(50) |zuname | varchar(50) | not nullgeburtsdatum | varchar(20)
|aktiv | boolean | not null default 't'::boolstrasse | varchar(255) |plz | varchar(25)
|ort | varchar(255) |telefon | varchar(255) |eintrittsdatum | varchar(20) |geschlechtid | bigint
| not null default 3treuelevelid | bigint | not null default 1clubmitglied | boolean | not null
default'f'::boolbemerkungen | text |mid | bigint |
Indices: mitglied_dlnummer_idx, [on dlnummer] mitglied_pkey [on mitgliedid]
--------------------
ok; i use 2 querys:
1) get one row over dlnummer:dl_online=# explain select * from mitglied where dlnummer = '098765432';NOTICE: QUERY
PLAN:IndexScan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1
width=154)EXPLAIN
2) get one row over the primatry key (mitgliedid):
dl_online=# explain select * from mitglied where mitgliedid = 833228;
NOTICE: QUERY PLAN:
Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154)
EXPLAIN
why doesn't use postrges in (2) the primary-key-index?? take a look at the
cost! and both queries returns only ONE row (the optimizer knows that fact).
and the worst. in(2) the query take ~3sec. in this time the cpu works on
99.9% (rh-linux 7 on a compac dc10 -alpha).
the other works fine (no time to calculate, "no" use of the cpu!).
PS: i tried also "set ENABLE_SEQSCAN to OFF". no chance, (2) will work with
Seq Scan.
thx to ANY hint!!
bernd.
