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 null
dlnummer | varchar(30) |
vorname | varchar(50) |
zuname | varchar(50) | not null
geburtsdatum | varchar(20) |
aktiv | boolean | not null default 't'::bool
strasse | varchar(255) |
plz | varchar(25) |
ort | varchar(255) |
telefon | varchar(255) |
eintrittsdatum | varchar(20) |
geschlechtid | bigint | not null default 3
treuelevelid | bigint | not null default 1
clubmitglied | boolean | not null default 'f'::bool
bemerkungen | 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:
Index Scan 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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
